DBMS EX1a

REM DROPPING TABLES

DROP TABLE  sungby;
DROP TABLE  artist;
DROP TABLE  song;
DROP TABLE  album;
DROP TABLE  studio;
DROP TABLE  musician;

REM CREATING TABLES

CREATE TABLE musician
(
musician_id NUMBER(5) CONSTRAINT pk_muscian_id PRIMARY KEY,
name VARCHAR2(25),
hometown VARCHAR2(25)
);
DESC musician;

CREATE TABLE studio
(
name VARCHAR2(15) CONSTRAINT pk_studio PRIMARY KEY,
address VARCHAR2(25),
homepage  VARCHAR2(20) CONSTRAINT ck_studio_hp CHECK( homepage like 'http://%'),
phone_no NUMBER(15) CONSTRAINT ck_phone CHECK(LENGTH (phone_no)=10)
);
DESC studio;

CREATE TABLE album
(
name VARCHAR2(25),
album_id NUMBER(5) CONSTRAINT pk_albumid PRIMARY KEY,
year NUMBER(4) CONSTRAINT ck_year CHECK(year>=1945),
no_of_tracks NUMBER(5) NOT NULL,
Studio_name VARCHAR2(15)  CONSTRAINT studio_name REFERENCES studio(name),
genre VARCHAR2(5) CONSTRAINT ck_albumgenre CHECK(genre in('CAR','DIV','MOV','POP')),
Musician_id NUMBER(5)  CONSTRAINT musicianid REFERENCES musician(musician_id)
);
DESC album;

CREATE TABLE song
(
album_id NUMBER(5) CONSTRAINT fk_albumid REFERENCES album(album_id),
track_id NUMBER(5) ,
length  NUMBER(5) ,
genre VARCHAR2(15) CONSTRAINT ch_sgenre CHECK(genre IN('PHI','REL','LOV','DEV','PAT')),
name VARCHAR2(15),
CONSTRAINT ch_length CHECK (genre not in ('PAT') or (length) > 7),
CONSTRAINT fk_song foreign key(album_id) REFERENCES album(album_id),
CONSTRAINT pk_song PRIMARY KEY(album_id,track_id)
);
DESC  song;

CREATE TABLE artist
(
artist_id NUMBER(5) CONSTRAINT pk_artistid PRIMARY KEY,
aname VARCHAR2(15)  CONSTRAINT uni_name UNIQUE,
hometown VARCHAR2(15),
homepage VARCHAR2(25) CONSTRAINT ck_artist_hp CHECK( homepage like 'http://%')
);
DESC artist;

CREATE TABLE sungby
(
Album_id NUMBER(5),
Artist_id NUMBER(5),
Track_id NUMBER(5),
recording_date DATE,
CONSTRAINT pk_sungby PRIMARY KEY (Album_id,Artist_id,Track_id),
CONSTRAINT fk_artistid FOREIGN KEY(Artist_id)REFERENCES artist(artist_id),
CONSTRAINT cfk FOREIGN KEY(Track_id,Album_id)REFERENCES song(track_id,album_id)
);
DESC sungby;

REM 1. The genre for Album can be generally categorized as CAR for Carnatic, DIV for Divine, MOV for Movies, POP for Pop songs.

INSERT INTO musician VALUES (100,'Harrish jeyaraj','chennai');
INSERT INTO musician VALUES (200,'A.R.Rahman','madurai');
INSERT INTO musician VALUES (400,'Ilayaraja','nagercoil');
INSERT INTO musician VALUES (500,'chitra','trinelveli');


INSERT INTO studio VALUES('AVM','Trichy','http://www.avm.com',8065985824);
INSERT INTO studio VALUES('Universal', 'USA','http://www.univ.com',8065987542);
INSERT INTO studio VALUES('Superkings','Trichy','http://www.avm.com',8876544824);
INSERT INTO studio VALUES('World', 'USA','http://www.univ.com',8643187542);

REM VALID CASE:

INSERT INTO album VALUES('My Elixir',101,2002,50,'AVM','CAR',100);
INSERT INTO album VALUES('Smashed',102,2008,60,'Universal','DIV',200);
INSERT INTO album VALUES('Little heart',106,2002,50,'Superkings','MOV',100);
INSERT INTO album VALUES('The king',107,2008,60,'World','POP',200);

REM INVALID CASE:

INSERT INTO album VALUES('Fair Weather Friends',184,2000,50,'AVM','AUL',100);


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.

INSERT INTO album VALUES('The India',108,2008,60,'World','POP',200);

REM VALID CASE:


INSERT INTO song VALUES(101,40,'Suttum',10,'PHI');
INSERT INTO song VALUES(102,50,'Kolaveri',12,'LOV');
INSERT INTO song VALUES(108,50,'Lala',10,'REL');
INSERT INTO song VALUES(106,40,'Silsil',12,'DEV');
INSERT INTO song VALUES(107,50,'Rara',12,'PAT');


REM INVALID CASE:

INSERT INTO song VALUES(103,25,'Vennilave',11,'WES');

REM 3. The artist ID, album ID, musician ID, and track number, studio name are used to retrieve tuple(s) individually from respective relations.

REM VALID CASE FOR ARTIST ID:
INSERT INTO artist VALUES(1000,'Ajith1','Madurai','http://www.alka.com');

REM INVALID CASE FOR ARTIST ID:
INSERT INTO artist VALUES(1000,'Ajith1','Madurai','http://www.alka.com');

REM VALID CASE FOR ALBUM ID:
INSERT INTO album VALUES('Friends',103,2000,50,'AVM','CAR',100);

REM INVALID CASE FOR ALBUM ID:
INSERT INTO album VALUES('Friends',103,2000,50,'AVM','CAR',100);

REM VALID CASE FOR MUSICIAN ID:
INSERT INTO musician VALUES (300,'Hari','chennai');

REM INVALID CASE FOR MUSICIAN ID:
INSERT INTO musician VALUES (300,'Harrish','chennai');

REM VALID CASE FOR STUDIO NAME:
INSERT INTO studio VALUES('Cine','Nagercoil','http://www.cine.com',8063475824);

REM INVALID CASE FOR STUDIO NAME:
INSERT INTO studio VALUES('Cine','Nagercoil','http://www.cine.com',8063475824);

REM VALID CASE FOR TRACK NUMBER:
INSERT INTO song VALUES(103,35,'Thuli',14,'DEV');

REM INVALID CASE FOR TRACK NUMBER:
INSERT INTO song VALUES(103,35,'Thuli',14,'DEV');

REM VALID CASE FOR SUNGBY:
INSERT INTO sungby VALUES(103,1000,35,'16-feb-2011');

REM INVALID CASE FOR SUNGBY:
INSERT INTO sungby VALUES(103,1000,35,'16-feb-2011');

REM 4. Ensure that the artist,musician, song, sungby and studio can not be removed without deleting the album details.

INSERT INTO album VALUES('Hinden',111,1985,25,'AVM','CAR',100);
INSERT INTO album VALUES('Hinden',109,1985,25,'AVM','CAR',100);
INSERT INTO song VALUES(111,44,'Thuli',14,'DEV');
INSERT INTO artist VALUES(222,'Ajith2','Madurai','http://www.alka.com');
INSERT INTO sungby VALUES(111,222,44,'16-feb-2011');
INSERT INTO musician VALUES (111,'Jeyaraj','chennai');
INSERT INTO studio VALUES('Success','Nagercoil','http://www.cine.com',8063475824);
INSERT INTO song VALUES(111,45,'solamal',14,'DEV');
INSERT INTO artist VALUES(208,'Anu','Madurai','http://www.alka.com');

REM VALID CASE :

DELETE FROM artist WHERE artist_id =208;
DELETE FROM musician WHERE musician_id =111;
DELETE FROM album WHERE album_id=109;
DELETE FROM studio WHERE name='Success';
DELETE FROM song WHERE track_id=45;

REM INVALID CASE:

DELETE FROM studio WHERE name = 'World';
DELETE FROM song WHERE album_id=111;
DELETE FROM musician WHERE musician_id =100;
DELETE FROM song WHERE track_id=44;
DELETE FROM artist WHERE artist_id =222;

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).

INSERT INTO artist VALUES(2000,'Ajitha','Madurai','http://www.alka.com');

REM VALID CASE- 1:                  

INSERT INTO artist VALUES(1001,'Arjun','Madras','http://www.arj.com');
INSERT INTO artist VALUES(1002,'Arun','Jaipur','http://www.arun.com');

INSERT INTO sungby VALUES(101,2000,40,'12-jan-2011');
INSERT INTO sungby VALUES(101,1001,40,'16-feb-2011');

REM VALID CASE- 2:              

INSERT INTO sungby VALUES(108,2000,50,'26-april-2011');
INSERT INTO sungby VALUES(103,2000,35,'22-may-2011');

REM VALID CASE- 3:                

INSERT INTO sungby VALUES(108,1001,50,'28-june-2011');
INSERT INTO sungby VALUES(106,1001,40,'6-july-2011');

REM INVALID CASE:
INSERT INTO sungby VALUES(101,1003,40,'6-july-2011');

REM  6. It was learnt that the artists do not have the same name.

REM VALID CASE:
INSERT INTO artist VALUES(1003,'Alka Ajith','Madurai','http://www.alka.com');
INSERT INTO artist VALUES(1004,'ChitraSivaraman','Sivakasi','http://www.chitra.com');

REM INVALID CASE:
INSERT INTO artist VALUES(1006,'Alka Ajith','Theni','http://www.alka1.com');

REM  7. The number of tracks in an album must always be recorded.

REM VALID CASE:
INSERT INTO album VALUES('Black Grape',104,1985,25,'AVM','CAR',100);

REM INVALID CASE:
INSERT INTO album VALUES('Killing Joke ',184,2020,NULL,'AVM','DIV',100);

REM  8. The length of each song must be greater than 7 for PAT songs.

REM VALID CASE:
INSERT INTO song VALUES(103,14,'Vaan Nila',18,'PAT');

REM INVALID CASE:
INSERT INTO song VALUES(103,53,'Vennilave',4,'PAT');

REM  9. The year of release of an album can not be earlier than 1945.

REM VALID CASE:
INSERT INTO album VALUES('Hindenburg',109,1985,25,'AVM','CAR',100);

REM INVALID CASE:
INSERT INTO album VALUES('Killing Joke ',184,1920,43,'AVM ','DIV',100);

REM  10.The phone number is of ten digits exactly.

REM VALID CASE:
INSERT INTO studio VALUES('Sony','London','http://www.sony.com',7668482324);

REM INVALID CASE:
INSERT INTO studio VALUES('TriStar','China','http://www.tri.in',919442732444);
INSERT INTO studio VALUES('Columbia', 'Mexico','http://www.col.com',226442);

REM  11. The homepage starts with “http:\\”

REM VALID CASE:
INSERT INTO studio VALUES('DreamWorks','London','http://www.sony.com',9876543246);

REM INVALID CASE:
INSERT INTO studio VALUES('Dream', 'Mexico','www.col.com',8976543127);

REM VALID CASE:
INSERT INTO artist VALUES(1005,'Donatello','Salem','http://www.don.com');

REM INVALID CASE:
INSERT INTO artist VALUES(1004,'Alka','Theni','www.alka1.com');

REM  12. It is necessary to represent the gender of an artist in the table.

DESC artist;
ALTER TABLE  artist ADD gender VARCHAR2(6);
DESC artist;

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).

DESC song;
ALTER TABLE song MODIFY  name  VARCHAR2(30);
DESC song;

REM VALID CASE:
INSERT INTO song VALUES(104,40,5,'LOV','Mouna Raagam');

REM  14. The phone number of each studio should be different.

ALTER TABLE studio MODIFY  phone_no UNIQUE;

REM VALID CASE:
INSERT INTO studio VALUES('Dreams', 'Mexico','http://www.col.com',8976543127);

REM INVALID CASE:
INSERT INTO studio VALUES('World', 'Canada','http://www.col.com',8976543127);

REM  15. An artist who sing a song for a particular track of an album can not be recorded
without the record_date.

DESC  sungby;
ALTER TABLE sungby MODIFY recording_date NOT NULL;
DESC  sungby;

REM VALID CASE:

INSERT INTO sungby VALUES(101,1000,40,'22-may-2011');

REM INVALID CASE:

INSERT INTO sungby VALUES(101,1000,60,NULL);

REM  16. It was decided to include the genre NAT for nature songs.

ALTER TABLE song DROP CONSTRAINT ch_sgenre;
ALTER TABLE song MODIFY genre VARCHAR2(5) CONSTRAINT ck_sgenre CHECK(genre in('REL','LOV','DEV','PHI','NAT','PAT') );

INSERT INTO song VALUES(106,35,7,'NAT','Thuli');

REM  17. It was found that homepage for the artist is not necessary.

DESC artist;
ALTER TABLE artist DROP COLUMN homepage;
DESC artist;
INSERT INTO artist VALUES(1009,'Ajith','Dindugal','male');
INSERT INTO artist VALUES(1017,'Chitras','Vellore','female');

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.

ALTER TABLE sungby DROP CONSTRAINT cfk;
ALTER TABLE sungby ADD CONSTRAINT fk_sung FOREIGN KEY(Track_id,Album_id)REFERENCES song(track_id,album_id)
ON DELETE CASCADE;

REM VALID CASE:

SELECT * FROM song;
SELECT * FROM sungby;

DELETE FROM  song WHERE album_id=108;

SELECT * FROM song;
SELECT * FROM sungby;