SQL> @ e:/assign1.sql;
SQL> REM DROPPING TABLES
SQL>
SQL> DROP TABLE sungby;
Table dropped.
SQL> DROP TABLE artist;
Table dropped.
SQL> DROP TABLE song;
Table dropped.
SQL> DROP TABLE album;
Table dropped.
SQL> DROP TABLE studio;
Table dropped.
SQL> DROP TABLE musician;
Table dropped.
SQL>
SQL> REM CREATING TABLES
SQL>
SQL> CREATE TABLE musician
2 (
3 musician_id NUMBER(5) CONSTRAINT pk_muscian_id PRIMARY KEY,
4 name VARCHAR2(25),
5 hometown VARCHAR2(25)
6 );
Table created.
SQL> DESC musician;
Name Null? Type
----------------------------------------- -------- ----------------------------
MUSICIAN_ID NOT NULL NUMBER(5)
NAME VARCHAR2(25)
HOMETOWN VARCHAR2(25)
SQL>
SQL> CREATE TABLE studio
2 (
3 name VARCHAR2(15) CONSTRAINT pk_studio PRIMARY KEY,
4 address VARCHAR2(25),
5 homepage VARCHAR2(20) CONSTRAINT ck_studio_hp CHECK( homepage like 'http://%'),
6 phone_no NUMBER(15) CONSTRAINT ck_phone CHECK(LENGTH (phone_no)=10)
7 );
Table created.
SQL> DESC studio;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME NOT NULL VARCHAR2(15)
ADDRESS VARCHAR2(25)
HOMEPAGE VARCHAR2(20)
PHONE_NO NUMBER(15)
SQL>
SQL> CREATE TABLE album
2 (
3 name VARCHAR2(25),
4 album_id NUMBER(5) CONSTRAINT pk_albumid PRIMARY KEY,
5 year NUMBER(4) CONSTRAINT ck_year CHECK(year>=1945),
6 no_of_tracks NUMBER(5) NOT NULL,
7 Studio_name VARCHAR2(15) CONSTRAINT studio_name REFERENCES studio(name),
8 genre VARCHAR2(5) CONSTRAINT ck_albumgenre CHECK(genre in('CAR','DIV','MOV','POP')),
9 Musician_id NUMBER(5) CONSTRAINT musicianid REFERENCES musician(musician_id)
10 );
Table created.
SQL> DESC album;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(25)
ALBUM_ID NOT NULL NUMBER(5)
YEAR NUMBER(4)
NO_OF_TRACKS NOT NULL NUMBER(5)
STUDIO_NAME VARCHAR2(15)
GENRE VARCHAR2(5)
MUSICIAN_ID NUMBER(5)
SQL>
SQL> CREATE TABLE song
2 (
3 album_id NUMBER(5) CONSTRAINT fk_albumid REFERENCES album(album_id),
4 track_id NUMBER(5) ,
5 length NUMBER(5) ,
6 genre VARCHAR2(15) CONSTRAINT ch_sgenre CHECK(genre IN('PHI','REL','LOV','DEV','PAT')),
7 name VARCHAR2(15),
8 CONSTRAINT ch_length CHECK (genre not in ('PAT') or (length) > 7),
9 CONSTRAINT fk_song foreign key(album_id) REFERENCES album(album_id),
10 CONSTRAINT pk_song PRIMARY KEY(album_id,track_id)
11 );
Table created.
SQL> DESC song;
Name Null? Type
----------------------------------------- -------- ----------------------------
ALBUM_ID NOT NULL NUMBER(5)
TRACK_ID NOT NULL NUMBER(5)
LENGTH NUMBER(5)
GENRE VARCHAR2(15)
NAME VARCHAR2(15)
SQL>
SQL> CREATE TABLE artist
2 (
3 artist_id NUMBER(5) CONSTRAINT pk_artistid PRIMARY KEY,
4 aname VARCHAR2(15) CONSTRAINT uni_name UNIQUE,
5 hometown VARCHAR2(15),
6 homepage VARCHAR2(25) CONSTRAINT ck_artist_hp CHECK( homepage like 'http://%')
7 );
Table created.
SQL> DESC artist;
Name Null? Type
----------------------------------------- -------- ----------------------------
ARTIST_ID NOT NULL NUMBER(5)
ANAME VARCHAR2(15)
HOMETOWN VARCHAR2(15)
HOMEPAGE VARCHAR2(25)
SQL>
SQL> CREATE TABLE sungby
2 (
3 Album_id NUMBER(5),
4 Artist_id NUMBER(5),
5 Track_id NUMBER(5),
6 recording_date DATE,
7 CONSTRAINT pk_sungby PRIMARY KEY (Album_id,Artist_id,Track_id),
8 CONSTRAINT fk_artistid FOREIGN KEY(Artist_id)REFERENCES artist(artist_id),
9 CONSTRAINT cfk FOREIGN KEY(Track_id,Album_id)REFERENCES song(track_id,album_id)
10 );
Table created.
SQL> DESC sungby;
Name Null? Type
----------------------------------------- -------- ----------------------------
ALBUM_ID NOT NULL NUMBER(5)
ARTIST_ID NOT NULL NUMBER(5)
TRACK_ID NOT NULL NUMBER(5)
RECORDING_DATE DATE
SQL>
SQL> REM 1. The genre for Album can be generally categorized as CAR for Carnatic, DIV for Divine, MOV for Movies, POP for Pop songs.
SQL>
SQL> INSERT INTO musician VALUES (100,'Harrish jeyaraj','chennai');
1 row created.
SQL> INSERT INTO musician VALUES (200,'A.R.Rahman','madurai');
1 row created.
SQL> INSERT INTO musician VALUES (400,'Ilayaraja','nagercoil');
1 row created.
SQL> INSERT INTO musician VALUES (500,'chitra','trinelveli');
1 row created.
SQL>
SQL>
SQL> INSERT INTO studio VALUES('AVM','Trichy','http://www.avm.com',8065985824);
1 row created.
SQL> INSERT INTO studio VALUES('Universal', 'USA','http://www.univ.com',8065987542);
1 row created.
SQL> INSERT INTO studio VALUES('Superkings','Trichy','http://www.avm.com',8876544824);
1 row created.
SQL> INSERT INTO studio VALUES('World', 'USA','http://www.univ.com',8643187542);
1 row created.
SQL>
SQL> REM VALID CASE:
SQL>
SQL> INSERT INTO album VALUES('My Elixir',101,2002,50,'AVM','CAR',100);
1 row created.
SQL> INSERT INTO album VALUES('Smashed',102,2008,60,'Universal','DIV',200);
1 row created.
SQL> INSERT INTO album VALUES('Little heart',106,2002,50,'Superkings','MOV',100);
1 row created.
SQL> INSERT INTO album VALUES('The king',107,2008,60,'World','POP',200);
1 row created.
SQL>
SQL> REM INVALID CASE:
SQL>
SQL> INSERT INTO album VALUES('Fair Weather Friends',184,2000,50,'AVM','AUL',100);
INSERT INTO album VALUES('Fair Weather Friends',184,2000,50,'AVM','AUL',100)
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.CK_ALBUMGENRE) violated
SQL>
SQL>
SQL> REM 2. The genre for Song can be PHI for philosophical, REL for relationship, LOV for duet, DEV for devotional, PAT for patriotic type of songs.
SQL>
SQL> INSERT INTO album VALUES('The India',108,2008,60,'World','POP',200);
1 row created.
SQL>
SQL> REM VALID CASE:
SQL>
SQL>
SQL> INSERT INTO song VALUES(101,40,'Suttum',10,'PHI');
INSERT INTO song VALUES(101,40,'Suttum',10,'PHI')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> INSERT INTO song VALUES(102,50,'Kolaveri',12,'LOV');
INSERT INTO song VALUES(102,50,'Kolaveri',12,'LOV')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> INSERT INTO song VALUES(108,50,'Lala',10,'REL');
INSERT INTO song VALUES(108,50,'Lala',10,'REL')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> INSERT INTO song VALUES(106,40,'Silsil',12,'DEV');
INSERT INTO song VALUES(106,40,'Silsil',12,'DEV')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> INSERT INTO song VALUES(107,50,'Rara',12,'PAT');
INSERT INTO song VALUES(107,50,'Rara',12,'PAT')
*
ERROR at line 1:
ORA-01722: invalid number
SQL>
SQL>
SQL> REM INVALID CASE:
SQL>
SQL> INSERT INTO song VALUES(103,25,'Vennilave',11,'WES');
INSERT INTO song VALUES(103,25,'Vennilave',11,'WES')
*
ERROR at line 1:
ORA-01722: invalid number
SQL>
SQL> REM 3. The artist ID, album ID, musician ID, and track number, studio name are used to retrieve tuple(s) individually from respective relations.
SQL>
SQL> REM VALID CASE FOR ARTIST ID:
SQL> INSERT INTO artist VALUES(1000,'Ajith1','Madurai','http://www.alka.com');
1 row created.
SQL>
SQL> REM INVALID CASE FOR ARTIST ID:
SQL> INSERT INTO artist VALUES(1000,'Ajith1','Madurai','http://www.alka.com');
INSERT INTO artist VALUES(1000,'Ajith1','Madurai','http://www.alka.com')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.PK_ARTISTID) violated
SQL>
SQL> REM VALID CASE FOR ALBUM ID:
SQL> INSERT INTO album VALUES('Friends',103,2000,50,'AVM','CAR',100);
1 row created.
SQL>
SQL> REM INVALID CASE FOR ALBUM ID:
SQL> INSERT INTO album VALUES('Friends',103,2000,50,'AVM','CAR',100);
INSERT INTO album VALUES('Friends',103,2000,50,'AVM','CAR',100)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.PK_ALBUMID) violated
SQL>
SQL> REM VALID CASE FOR MUSICIAN ID:
SQL> INSERT INTO musician VALUES (300,'Hari','chennai');
1 row created.
SQL>
SQL> REM INVALID CASE FOR MUSICIAN ID:
SQL> INSERT INTO musician VALUES (300,'Harrish','chennai');
INSERT INTO musician VALUES (300,'Harrish','chennai')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.PK_MUSCIAN_ID) violated
SQL>
SQL> REM VALID CASE FOR STUDIO NAME:
SQL> INSERT INTO studio VALUES('Cine','Nagercoil','http://www.cine.com',8063475824);
1 row created.
SQL>
SQL> REM INVALID CASE FOR STUDIO NAME:
SQL> INSERT INTO studio VALUES('Cine','Nagercoil','http://www.cine.com',8063475824);
INSERT INTO studio VALUES('Cine','Nagercoil','http://www.cine.com',8063475824)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.PK_STUDIO) violated
SQL>
SQL> REM VALID CASE FOR TRACK NUMBER:
SQL> INSERT INTO song VALUES(103,35,'Thuli',14,'DEV');
INSERT INTO song VALUES(103,35,'Thuli',14,'DEV')
*
ERROR at line 1:
ORA-01722: invalid number
SQL>
SQL> REM INVALID CASE FOR TRACK NUMBER:
SQL> INSERT INTO song VALUES(103,35,'Thuli',14,'DEV');
INSERT INTO song VALUES(103,35,'Thuli',14,'DEV')
*
ERROR at line 1:
ORA-01722: invalid number
SQL>
SQL> REM VALID CASE FOR SUNGBY:
SQL> INSERT INTO sungby VALUES(103,1000,35,'16-feb-2011');
INSERT INTO sungby VALUES(103,1000,35,'16-feb-2011')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CFK) violated - parent key not found
SQL>
SQL> REM INVALID CASE FOR SUNGBY:
SQL> INSERT INTO sungby VALUES(103,1000,35,'16-feb-2011');
INSERT INTO sungby VALUES(103,1000,35,'16-feb-2011')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CFK) violated - parent key not found
SQL>
SQL> REM 4. Ensure that the artist,musician, song, sungby and studio can not be removed without deleting the album details.
SQL>
SQL> INSERT INTO album VALUES('Hinden',111,1985,25,'AVM','CAR',100);
1 row created.
SQL> INSERT INTO album VALUES('Hinden',109,1985,25,'AVM','CAR',100);
1 row created.
SQL> INSERT INTO song VALUES(111,44,'Thuli',14,'DEV');
INSERT INTO song VALUES(111,44,'Thuli',14,'DEV')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> INSERT INTO artist VALUES(222,'Ajith2','Madurai','http://www.alka.com');
1 row created.
SQL> INSERT INTO sungby VALUES(111,222,44,'16-feb-2011');
INSERT INTO sungby VALUES(111,222,44,'16-feb-2011')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CFK) violated - parent key not found
SQL> INSERT INTO musician VALUES (111,'Jeyaraj','chennai');
1 row created.
SQL> INSERT INTO studio VALUES('Success','Nagercoil','http://www.cine.com',8063475824);
1 row created.
SQL> INSERT INTO song VALUES(111,45,'solamal',14,'DEV');
INSERT INTO song VALUES(111,45,'solamal',14,'DEV')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> INSERT INTO artist VALUES(208,'Anu','Madurai','http://www.alka.com');
1 row created.
SQL>
SQL> REM VALID CASE :
SQL>
SQL> DELETE FROM artist WHERE artist_id =208;
1 row deleted.
SQL> DELETE FROM musician WHERE musician_id =111;
1 row deleted.
SQL> DELETE FROM album WHERE album_id=109;
1 row deleted.
SQL> DELETE FROM studio WHERE name='Success';
1 row deleted.
SQL> DELETE FROM song WHERE track_id=45;
0 rows deleted.
SQL>
SQL> REM INVALID CASE:
SQL>
SQL> DELETE FROM studio WHERE name = 'World';
DELETE FROM studio WHERE name = 'World'
*
ERROR at line 1:
ORA-02292: integrity constraint (SYSTEM.STUDIO_NAME) violated - child record
found
SQL> DELETE FROM song WHERE album_id=111;
0 rows deleted.
SQL> DELETE FROM musician WHERE musician_id =100;
DELETE FROM musician WHERE musician_id =100
*
ERROR at line 1:
ORA-02292: integrity constraint (SYSTEM.MUSICIANID) violated - child record
found
SQL> DELETE FROM song WHERE track_id=44;
0 rows deleted.
SQL> DELETE FROM artist WHERE artist_id =222;
1 row deleted.
SQL>
SQL> REM 5. A song may be sung by more than one artist. The same artist may sing for more than one track in the same album. Similarly an artist can sing for different album(s).
SQL>
SQL> INSERT INTO artist VALUES(2000,'Ajitha','Madurai','http://www.alka.com');
1 row created.
SQL>
SQL> REM VALID CASE- 1:
SQL>
SQL> INSERT INTO artist VALUES(1001,'Arjun','Madras','http://www.arj.com');
1 row created.
SQL> INSERT INTO artist VALUES(1002,'Arun','Jaipur','http://www.arun.com');
1 row created.
SQL>
SQL> INSERT INTO sungby VALUES(101,2000,40,'12-jan-2011');
INSERT INTO sungby VALUES(101,2000,40,'12-jan-2011')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CFK) violated - parent key not found
SQL> INSERT INTO sungby VALUES(101,1001,40,'16-feb-2011');
INSERT INTO sungby VALUES(101,1001,40,'16-feb-2011')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CFK) violated - parent key not found
SQL>
SQL> REM VALID CASE- 2:
SQL>
SQL> INSERT INTO sungby VALUES(108,2000,50,'26-april-2011');
INSERT INTO sungby VALUES(108,2000,50,'26-april-2011')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CFK) violated - parent key not found
SQL> INSERT INTO sungby VALUES(103,2000,35,'22-may-2011');
INSERT INTO sungby VALUES(103,2000,35,'22-may-2011')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CFK) violated - parent key not found
SQL>
SQL> REM VALID CASE- 3:
SQL>
SQL> INSERT INTO sungby VALUES(108,1001,50,'28-june-2011');
INSERT INTO sungby VALUES(108,1001,50,'28-june-2011')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CFK) violated - parent key not found
SQL> INSERT INTO sungby VALUES(106,1001,40,'6-july-2011');
INSERT INTO sungby VALUES(106,1001,40,'6-july-2011')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CFK) violated - parent key not found
SQL>
SQL> REM INVALID CASE:
SQL> INSERT INTO sungby VALUES(101,1003,40,'6-july-2011');
INSERT INTO sungby VALUES(101,1003,40,'6-july-2011')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CFK) violated - parent key not found
SQL>
SQL> REM 6. It was learnt that the artists do not have the same name.
SQL>
SQL> REM VALID CASE:
SQL> INSERT INTO artist VALUES(1003,'Alka Ajith','Madurai','http://www.alka.com');
1 row created.
SQL> INSERT INTO artist VALUES(1004,'ChitraSivaraman','Sivakasi','http://www.chitra.com');
1 row created.
SQL>
SQL> REM INVALID CASE:
SQL> INSERT INTO artist VALUES(1006,'Alka Ajith','Theni','http://www.alka1.com');
INSERT INTO artist VALUES(1006,'Alka Ajith','Theni','http://www.alka1.com')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.UNI_NAME) violated
SQL>
SQL> REM 7. The number of tracks in an album must always be recorded.
SQL>
SQL> REM VALID CASE:
SQL> INSERT INTO album VALUES('Black Grape',104,1985,25,'AVM','CAR',100);
1 row created.
SQL>
SQL> REM INVALID CASE:
SQL> INSERT INTO album VALUES('Killing Joke ',184,2020,NULL,'AVM','DIV',100);
INSERT INTO album VALUES('Killing Joke ',184,2020,NULL,'AVM','DIV',100)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."ALBUM"."NO_OF_TRACKS")
SQL>
SQL> REM 8. The length of each song must be greater than 7 for PAT songs.
SQL>
SQL> REM VALID CASE:
SQL> INSERT INTO song VALUES(103,14,'Vaan Nila',18,'PAT');
INSERT INTO song VALUES(103,14,'Vaan Nila',18,'PAT')
*
ERROR at line 1:
ORA-01722: invalid number
SQL>
SQL> REM INVALID CASE:
SQL> INSERT INTO song VALUES(103,53,'Vennilave',4,'PAT');
INSERT INTO song VALUES(103,53,'Vennilave',4,'PAT')
*
ERROR at line 1:
ORA-01722: invalid number
SQL>
SQL> REM 9. The year of release of an album can not be earlier than 1945.
SQL>
SQL> REM VALID CASE:
SQL> INSERT INTO album VALUES('Hindenburg',109,1985,25,'AVM','CAR',100);
1 row created.
SQL>
SQL> REM INVALID CASE:
SQL> INSERT INTO album VALUES('Killing Joke ',184,1920,43,'AVM ','DIV',100);
INSERT INTO album VALUES('Killing Joke ',184,1920,43,'AVM ','DIV',100)
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.CK_YEAR) violated
SQL>
SQL> REM 10.The phone number is of ten digits exactly.
SQL>
SQL> REM VALID CASE:
SQL> INSERT INTO studio VALUES('Sony','London','http://www.sony.com',7668482324);
1 row created.
SQL>
SQL> REM INVALID CASE:
SQL> INSERT INTO studio VALUES('TriStar','China','http://www.tri.in',919442732444);
INSERT INTO studio VALUES('TriStar','China','http://www.tri.in',919442732444)
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.CK_PHONE) violated
SQL> INSERT INTO studio VALUES('Columbia', 'Mexico','http://www.col.com',226442);
INSERT INTO studio VALUES('Columbia', 'Mexico','http://www.col.com',226442)
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.CK_PHONE) violated
SQL>
SQL> REM 11. The homepage starts with “http:\\”
SQL>
SQL> REM VALID CASE:
SQL> INSERT INTO studio VALUES('DreamWorks','London','http://www.sony.com',9876543246);
1 row created.
SQL>
SQL> REM INVALID CASE:
SQL> INSERT INTO studio VALUES('Dream', 'Mexico','www.col.com',8976543127);
INSERT INTO studio VALUES('Dream', 'Mexico','www.col.com',8976543127)
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.CK_STUDIO_HP) violated
SQL>
SQL> REM VALID CASE:
SQL> INSERT INTO artist VALUES(1005,'Donatello','Salem','http://www.don.com');
1 row created.
SQL>
SQL> REM INVALID CASE:
SQL> INSERT INTO artist VALUES(1004,'Alka','Theni','www.alka1.com');
INSERT INTO artist VALUES(1004,'Alka','Theni','www.alka1.com')
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.CK_ARTIST_HP) violated
SQL>
SQL> REM 12. It is necessary to represent the gender of an artist in the table.
SQL>
SQL> DESC artist;
Name Null? Type
----------------------------------------- -------- ----------------------------
ARTIST_ID NOT NULL NUMBER(5)
ANAME VARCHAR2(15)
HOMETOWN VARCHAR2(15)
HOMEPAGE VARCHAR2(25)
SQL> ALTER TABLE artist ADD gender VARCHAR2(6);
Table altered.
SQL> DESC artist;
Name Null? Type
----------------------------------------- -------- ----------------------------
ARTIST_ID NOT NULL NUMBER(5)
ANAME VARCHAR2(15)
HOMETOWN VARCHAR2(15)
HOMEPAGE VARCHAR2(25)
GENDER VARCHAR2(6)
SQL>
SQL> REM 13. The first few words of the lyrics constitute the song name. The song name do not accommodate some of the REM words (in lyrics).
SQL>
SQL> DESC song;
Name Null? Type
----------------------------------------- -------- ----------------------------
ALBUM_ID NOT NULL NUMBER(5)
TRACK_ID NOT NULL NUMBER(5)
LENGTH NUMBER(5)
GENRE VARCHAR2(15)
NAME VARCHAR2(15)
SQL> ALTER TABLE song MODIFY name VARCHAR2(30);
Table altered.
SQL> DESC song;
Name Null? Type
----------------------------------------- -------- ----------------------------
ALBUM_ID NOT NULL NUMBER(5)
TRACK_ID NOT NULL NUMBER(5)
LENGTH NUMBER(5)
GENRE VARCHAR2(15)
NAME VARCHAR2(30)
SQL>
SQL> REM VALID CASE:
SQL> INSERT INTO song VALUES(104,40,5,'LOV','Mouna Raagam');
1 row created.
SQL>
SQL> REM 14. The phone number of each studio should be different.
SQL>
SQL> ALTER TABLE studio MODIFY phone_no UNIQUE;
Table altered.
SQL>
SQL> REM VALID CASE:
SQL> INSERT INTO studio VALUES('Dreams', 'Mexico','http://www.col.com',8976543127);
1 row created.
SQL>
SQL> REM INVALID CASE:
SQL> INSERT INTO studio VALUES('World', 'Canada','http://www.col.com',8976543127);
INSERT INTO studio VALUES('World', 'Canada','http://www.col.com',8976543127)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.PK_STUDIO) violated
SQL>
SQL> REM 15. An artist who sing a song for a particular track of an album can not be recorded
SQL> without the record_date.
SP2-0734: unknown command beginning "without th..." - rest of line ignored.
SQL>
SQL> DESC sungby;
Name Null? Type
----------------------------------------- -------- ----------------------------
ALBUM_ID NOT NULL NUMBER(5)
ARTIST_ID NOT NULL NUMBER(5)
TRACK_ID NOT NULL NUMBER(5)
RECORDING_DATE DATE
SQL> ALTER TABLE sungby MODIFY recording_date NOT NULL;
Table altered.
SQL> DESC sungby;
Name Null? Type
----------------------------------------- -------- ----------------------------
ALBUM_ID NOT NULL NUMBER(5)
ARTIST_ID NOT NULL NUMBER(5)
TRACK_ID NOT NULL NUMBER(5)
RECORDING_DATE NOT NULL DATE
SQL>
SQL> REM VALID CASE:
SQL>
SQL> INSERT INTO sungby VALUES(101,1000,40,'22-may-2011');
INSERT INTO sungby VALUES(101,1000,40,'22-may-2011')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CFK) violated - parent key not found
SQL>
SQL> REM INVALID CASE:
SQL>
SQL> INSERT INTO sungby VALUES(101,1000,60,NULL);
INSERT INTO sungby VALUES(101,1000,60,NULL)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."SUNGBY"."RECORDING_DATE")
SQL>
SQL> REM 16. It was decided to include the genre NAT for nature songs.
SQL>
SQL> ALTER TABLE song DROP CONSTRAINT ch_sgenre;
Table altered.
SQL> ALTER TABLE song MODIFY genre VARCHAR2(5) CONSTRAINT ck_sgenre CHECK(genre in('REL','LOV','DEV','PHI','NAT','PAT') );
Table altered.
SQL>
SQL> INSERT INTO song VALUES(106,35,7,'NAT','Thuli');
1 row created.
SQL>
SQL> REM 17. It was found that homepage for the artist is not necessary.
SQL>
SQL> DESC artist;
Name Null? Type
----------------------------------------- -------- ----------------------------
ARTIST_ID NOT NULL NUMBER(5)
ANAME VARCHAR2(15)
HOMETOWN VARCHAR2(15)
HOMEPAGE VARCHAR2(25)
GENDER VARCHAR2(6)
SQL> ALTER TABLE artist DROP COLUMN homepage;
Table altered.
SQL> DESC artist;
Name Null? Type
----------------------------------------- -------- ----------------------------
ARTIST_ID NOT NULL NUMBER(5)
ANAME VARCHAR2(15)
HOMETOWN VARCHAR2(15)
GENDER VARCHAR2(6)
SQL> INSERT INTO artist VALUES(1009,'Ajith','Dindugal','male');
1 row created.
SQL> INSERT INTO artist VALUES(1017,'Chitras','Vellore','female');
1 row created.
SQL>
SQL> REM 18. Due to typo-error, there may be a possibility of false information. Hence while deleting the information pertained to a song, make sure that REM all the corresponding details are also deleted.
SQL>
SQL> ALTER TABLE sungby DROP CONSTRAINT cfk;
Table altered.
SQL> ALTER TABLE sungby ADD CONSTRAINT fk_sung FOREIGN KEY(Track_id,Album_id)REFERENCES song(track_id,album_id)
2 ON DELETE CASCADE;
Table altered.
SQL>
SQL> REM VALID CASE:
SQL>
SQL> SELECT * FROM song;
ALBUM_ID TRACK_ID LENGTH GENRE NAME
---------- ---------- ---------- ----- ------------------------------
104 40 5 LOV Mouna Raagam
106 35 7 NAT Thuli
SQL> SELECT * FROM sungby;
no rows selected
SQL>
SQL> DELETE FROM song WHERE album_id=108;
0 rows deleted.
SQL>
SQL> SELECT * FROM song;
ALBUM_ID TRACK_ID LENGTH GENRE NAME
---------- ---------- ---------- ----- ------------------------------
104 40 5 LOV Mouna Raagam
106 35 7 NAT Thuli
SQL> SELECT * FROM sungby;
no rows selected
SQL> spool off;
No comments:
Post a Comment