 Updatable views + Key preserved table (merged) [message #315601] |
Tue, 22 April 2008 02:24  |
|
|
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  |
pablolee
Messages: 2083
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 |
|
|
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 |
 |
Michel Cadot
Messages: 54236
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 |
pablolee
Messages: 2083
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 |
|
|
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 |
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.
|
|
|
|
|
|
| newbies on oracle [message #316441 is a reply to message #315601] |
Fri, 25 April 2008 02:35 |
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 |
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 |
|
|
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 #320108 is a reply to message #320091] |
Wed, 14 May 2008 02:13 |
|
|
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.
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
