Home » Other » Training & Certification » Updatable views + Key preserved table (merged)
icon6.gif  Updatable views + Key preserved table (merged) [message #315601] Tue, 22 April 2008 02:24 Go to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member



Hi,

Kindly provide which one would be correct for the following:

Some explanation would be helpful.

The primary key of the STATE table is STATE_CD. The primary key of the CITY table is STATE_CD and CITY_CD. The STATE_CD column of the CITY table is the foreign key to the STATE table. There are no other constraints on these two tables. Consider the following view definition.

CREATE OR REPLACE VIEW state_city AS
SELECT a.state_cd, a.state_name, b.city_cd, b.city_name
FROM state a, city b
WHERE a.state_cd = b.state_cd;

Which of the following operations are permitted on the base tables of the view? (Choose all that apply.)

A. Insert a record into the CITY table

B. Insert a record into the STATE table

C. Update the STATE_CD column of the CITY table

D. Update the CITY_CD column of the CITY table

E. Update the CITY_NAME column of the CITY table

F. Update the STATE_NAME column of the STATE table



Thanks,
Priya.




Re: Updatable views [message #315603 is a reply to message #315601] Tue, 22 April 2008 02:45 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
This should have been posted in the Homework section. You tell us what you thisnk the answer(s) should be and why, and we'll tell you if that is correct ot not, but we won't do your homework for you.
Re: Updatable views [message #315608 is a reply to message #315603] Tue, 22 April 2008 03:04 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member



Hi,

As per considering the view below:

CREATE OR REPLACE VIEW state_city AS
SELECT a.state_cd, a.state_name, b.city_cd, b.city_name
FROM state a, city b
WHERE a.state_cd = b.state_cd;

A. Insert a record into the CITY table
This cannot be true as b.state_cd is not part of the view.
B. Insert a record into the STATE table
This can be true.
C. Update the STATE_CD column of the CITY table
This cannot be true as b.state_cd is not part of the view.
D. Update the CITY_CD column of the CITY table
This can be true.
E. Update the CITY_NAME column of the CITY table
This can be true.
F. Update the STATE_NAME column of the STATE table
This can be true.


Thanks,
Priya.

Re: Updatable views [message #315611 is a reply to message #315608] Tue, 22 April 2008 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just try it:
SQL> insert into state_city (state_cd,city_cd,city_name) values (0,0,'Paris');
insert into state_city (state_cd,city_cd,city_name) values (0,0,'Paris')
                        *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SQL> insert into state_city (state_cd,state_name) values (1,'USA');
insert into state_city (state_cd,state_name) values (1,'USA')
                        *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SQL> update state_city set city_cd=0;

0 rows updated.

SQL> update state_city set city_name='None';

0 rows updated.

SQL> update state_city set state_name='None';
update state_city set state_name='None'
                      *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Regards
Michel
Re: Updatable views [message #315613 is a reply to message #315608] Tue, 22 April 2008 03:28 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
I would want clarification on the question:
Quote:
Which of the following operations are permitted on the base tables of the view? (Choose all that apply.)

I could say that given the appropriate permissions, I could perform all of these tasks on the base tables (The question does not stipulate that the tasks be performed via the view) I would check that the teacher is not an awkward b*&Y&^ and is giving a trick question (unlikely, but you never know) Other than that, do as Michel says
Re: Updatable views [message #315615 is a reply to message #315601] Tue, 22 April 2008 03:33 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member



Hi,

As per considering the examples and view below:

Can we conclude:

CREATE OR REPLACE VIEW state_city AS
SELECT a.state_cd, a.state_name, b.city_cd, b.city_name
FROM state a, city b
WHERE a.state_cd = b.state_cd;

A. Insert a record into the CITY table
FALSE

B. Insert a record into the STATE table
FALSE

C. Update the STATE_CD column of the CITY table
FALSE

D. Update the CITY_CD column of the CITY table
TRUE

E. Update the CITY_NAME column of the CITY table
TRUE

F. Update the STATE_NAME column of the STATE table
FALSE

Michel Cadot:
Can you give me more idea about what does:
"ORA-01779: cannot modify a column which maps to a non key-preserved table"

Thanks,
Priya.


Re: Updatable views [message #315620 is a reply to message #315615] Tue, 22 April 2008 03:59 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
priyamalhotra wrote on Tue, 22 April 2008 10:33

Can you give me more idea about what does:
"ORA-01779: cannot modify a column which maps to a non key-preserved table"


Did you perform any kind of search on that error? Google? Search this site?
What did it return that you do not understand?

Remember: the basis of working in software development is to find your way through documentation and information.
Re: Updatable views [message #315626 is a reply to message #315615] Tue, 22 April 2008 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AskTom: Key-preserved table concept in join view

Regards
Michel
newbies on oracle [message #316441 is a reply to message #315601] Fri, 25 April 2008 02:35 Go to previous messageGo to next message
wzm04
Messages: 2
Registered: April 2008
Junior Member
Hi,I am new here. I used to learn SQL server 2005 in my school but I never learn oracle so it is kinda new to me. But now my job need me to learn Oracle database. I already see the oracle and PL/SQL Developer 5.0.1(as the database connection)interface but it is different from SQL server 2005,it is more complicated. Are there any fast free online training for this two program? I also would like to know how many program languange did PL/SQL Developer 5.0.1 support(I see that it support SQL Plus and SQL languange)and I would like to ask how to convert .bin DBASE III Plus IBM to the Oracle Database?Sorry for many question.Thanks
Re: newbies on oracle [message #317158 is a reply to message #316441] Tue, 29 April 2008 13:55 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
wzm04 wrote on Fri, 25 April 2008 03:35
Hi,I am new here. I used to learn SQL server 2005 in my school but I never learn oracle so it is kinda new to me. But now my job need me to learn Oracle database.


These are most important:
PL/SQL
Concepts (i.e., how Oracle db works)

You can find other reference here:
Oracle books

Quote:
I already see the oracle and PL/SQL Developer 5.0.1(as the database connection)interface but it is different from SQL server 2005,it is more complicated. Are there any fast free online training for this two program?



PL/SQL Developer has a very good help/user manual. You may also want to try using TOAD (www.quest.com). It's extremely powerful, and will be more intuitive to start with, especially coming from MS-SQL Server.
Key Preserved Table [message #320083 is a reply to message #315601] Wed, 14 May 2008 01:19 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member


Hi,

How to identify the key preserved table using the following informaiton:

The primary key of the STATE table is STATE_CD. The primary key of the CITY table is STATE_CD and CITY_CD. The STATE_CD column of the CITY table is the foreign key to the STATE table. There are no other constraints on these two tables. Consider the following view definition.

i.e.

STATE
--------
STATE_CD VARCHAR2(10) PRIMARY KEY,
STATE_NAME VARCHAR2(20)

CITY
------
STATE_CD VARCHAR2(10),
CITY_CD VARCHAR2(10),
PRIMARY KEY (STATE_CD, CITY_CD),
FOREIGN KEY CITY_CD REFERENCES STATE(STATE_CD)

& then a view is created as

CREATE OR REPLACE VIEW state_city AS
SELECT a.state_cd, a.state_name, b.city_cd, b.city_name
FROM state a, city b
WHERE a.state_cd = b.state_cd;

Then which is the key preserved table in the above view.

Thanks,
Priya.

Re: Key Preserved Table [message #320091 is a reply to message #320083] Wed, 14 May 2008 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AskTom: Key-preserved table concept in join view

Regards
Michel
icon10.gif  Re: Key Preserved Table [message #320108 is a reply to message #320091] Wed, 14 May 2008 02:13 Go to previous message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member


Hi,

Thanks for the useful link and considering the explanation can we conclude?? that:

STATE table is a key preserved table as all the primary (STATE.STATE_CD) and unique columns from the STATE table are part of the view.

While the CITY table having primary key (STATE_CD, CITY_CD) is not a key preserved table as STATE_CD is not part of the view:

CREATE OR REPLACE VIEW state_city AS
SELECT a.state_cd, a.state_name, b.city_cd, b.city_name
FROM state a, city b
WHERE a.state_cd = b.state_cd;

Thanks,
Priya.

Previous Topic: how can i catch prime numbers with While Loop ?
Next Topic: Beginner database designer/dba
Goto Forum:
  


Current Time: Wed Apr 16 17:40:30 CDT 2014

Total time taken to generate the page: 0.17070 seconds