RelationalEx2.sql

ALTER TABLE STAFF DROP CONSTRAINT FK_BRANCH_STAFF;
ALTER TABLE REGISTRATION DROP CONSTRAINT FK_BRANCH_REG;
ALTER TABLE REGISTRATION DROP CONSTRAINT FK_MEMBER_REG;
ALTER TABLE REGISTRATION DROP CONSTRAINT FK_STAFF_REG;
ALTER TABLE VIDEO DROP CONSTRAINT DIRECTOR_VIDEO;
ALTER TABLE ROLE DROP CONSTRAINT VIDEO_ROLE;
ALTER TABLE ROLE DROP CONSTRAINT ACTOR_ROLE;
ALTER TABLE VIDEOFORRENT DROP CONSTRAINT FK_VIDEO_VFR;
ALTER TABLE VIDEOFORRENT DROP CONSTRAINT FK_BRANCH_VFR;
ALTER TABLE RENTALAGREEMENT DROP CONSTRAINT FK_VFR_RA;
ALTER TABLE RENTALAGREEMENT DROP CONSTRAINT FK_MEMBER_RA;

DROP TABLE ACTOR;
DROP TABLE BRANCH_EX2;
DROP TABLE DIRECTOR;
DROP TABLE MEMBER;
DROP TABLE STAFF;
DROP TABLE REGISTRATION;
DROP TABLE RENTALAGREEMENT;
DROP TABLE ROLE;
DROP TABLE VIDEO;
DROP TABLE VIDEOFORRENT;

CREATE TABLE ACTOR
( ACTORNO VARCHAR2(6)PRIMARY KEY,
  ACTORNAME VARCHAR(40)
 );
 
 CREATE TABLE BRANCH_EX2 (
   BRANCHNO VARCHAR2(6),
   STREET VARCHAR2(30),
   CITY VARCHAR2(40),
   STATE VARCHAR(15),
   ZIPCODE NUMBER,
   MGRSTAFFNO VARCHAR(5),
   PRIMARY KEY (BRANCHNO)
 );
 
 CREATE TABLE DIRECTOR (
   DIRECTORNO VARCHAR2(5) PRIMARY KEY,
   DIRECTORNAME VARCHAR2(45)
 );
 
 CREATE TABLE MEMBER (
   MEMBERNO VARCHAR2(8) PRIMARY KEY,
   FNAME VARCHAR2(20),
   LNAME VARCHAR2(30),
   ADDRESS VARCHAR (50)
 );
 
 CREATE TABLE STAFF (
   STAFFNO VARCHAR2(6) PRIMARY KEY,
   NAME VARCHAR2(40),
   POSITION VARCHAR2(12),
   SALARY NUMBER(8,2) DEFAULT 0,
   BRANCHNO VARCHAR2(6),
   CONSTRAINT FK_BRANCH_STAFF FOREIGN KEY (BRANCHNO) REFERENCES BRANCH_EX2(BRANCHNO) 
 );
  
 CREATE TABLE VIDEO (
   CATALOGNO  NUMBER PRIMARY KEY,
   TITLE VARCHAR2(40) NOT NULL,
   CATEGORY VARCHAR2(20) NOT NULL,
   DAYLYRENTAL NUMBER,
   PRICE NUMBER (6,2),
   DIRECTORNO VARCHAR2(5),
   CONSTRAINT DIRECTOR_VIDEO FOREIGN KEY(DIRECTORNO) REFERENCES DIRECTOR(DIRECTORNO)
 
 );
 
 CREATE TABLE REGISTRATION (
   BRANCHNO VARCHAR2(6),
   MEMBERNO VARCHAR2(8),
   STAFFNO VARCHAR2(6) NULL,
   DATEJOINED DATE,
   PRIMARY KEY (BRANCHNO,MEMBERNO),--,STAFFNO),
   CONSTRAINT FK_BRANCH_REG FOREIGN KEY (BRANCHNO) REFERENCES BRANCH_EX2(BRANCHNO),
   CONSTRAINT FK_MEMBER_REG FOREIGN KEY (MEMBERNO) REFERENCES MEMBER(MEMBERNO),
   CONSTRAINT FK_STAFF_REG FOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO)
 );
 
 
 
 CREATE TABLE ROLE (
   ACTORNO VARCHAR2(6) PRIMARY KEY,
   CATALOGNO NUMBER NOT NULL,
   CHARACTER VARCHAR2(40),
   CONSTRAINT ACTOR_ROLE FOREIGN KEY (ACTORNO) REFERENCES ACTOR(ACTORNO),
   CONSTRAINT VIDEO_ROLE FOREIGN KEY(CATALOGNO) REFERENCES VIDEO(CATALOGNO)
 ); 
 
 CREATE TABLE VIDEOFORRENT (
 VIDEONO NUMBER PRIMARY KEY,
 AVAILABLE CHAR(1) CHECK(AVAILABLE in('Y','N')),
 CATALOGNO NUMBER NOT NULL,
 BRANCHNO VARCHAR2(6),
 CONSTRAINT FK_VIDEO_VFR FOREIGN KEY (CATALOGNO) REFERENCES VIDEO(CATALOGNO),
 CONSTRAINT FK_BRANCH_VFR FOREIGN KEY (BRANCHNO) REFERENCES BRANCH_EX2(BRANCHNO)
 );
 
 CREATE TABLE RENTALAGREEMENT (
 RENTALNO VARCHAR2(8) PRIMARY KEY,
 DATEOUT DATE DEFAULT TO_DATE(SYSDATE,'YYYY MM DD'),
 DATERETURN DATE NULL,
 MEMBERNO VARCHAR2(8) NOT NULL,
 VIDEONO NUMBER NOT NULL,
 CONSTRAINT FK_VFR_RA FOREIGN KEY (VIDEONO) REFERENCES VIDEOFORRENT (VIDEONO),
 CONSTRAINT FK_MEMBER_RA FOREIGN KEY (MEMBERNO) REFERENCES MEMBER(MEMBERNO)
 );
 
ALTER TABLE BRANCH_EX2 
ADD CONSTRAINT FK_STAFF_BRANCH FOREIGN KEY(MGRSTAFFNO) REFERENCES STAFF(STAFFNO);

INSERT INTO MEMBER VALUES('M111111','Niels','Olsen','Byvej 10, 1000 København');
INSERT INTO BRANCH_EX2 VALUES('B005','Bagsvær hovedgade 81','Gladsaxe','DK',2880,NULL);
INSERT INTO STAFF VALUES('S2240','Vakse Viggo','Manager',30000,'B005');
UPDATE BRANCH_EX2 SET MGRSTAFFNO ='S2240' WHERE BRANCHNO = 'B005';

INSERT INTO REGISTRATION(BRANCHNO,MEMBERNO,DATEJOINED) VALUES ('B005','M111111',TO_DATE('2001-09-08','YYYY MM DD'));

INSERT INTO DIRECTOR VALUES('D3000','Andy Tennart');
INSERT INTO VIDEO VALUES (222222,'The Bounty Hunter','Action',5,29.99,'D3000');
INSERT INTO VIDEOFORRENT VALUES (123456,'Y',222222,'B005');
INSERT INTO RENTALAGREEMENT VALUES ('R666666',SYSDATE,NULL,'M111111',123456);
INSERT INTO ACTOR VALUES('A4000','Jennifer Aniston');
INSERT INTO ROLE VALUES('A4000',222222,'Nichole');



UPDATE STAFF SET BRANCHNO =NULL WHERE STAFFNO = 'S2240';
--DELETE FROM BRANCH_EX2 WHERE BRANCHNO='B005';
SELECT * FROM BRANCH_EX2;