exmple of a relation database in sql for a video hire shop
DATABASES - ASSIGNMENT 2 This document contains the SQL source code for all the questions in the assignment 2 of the Databases course. It could also be seen as a report to the Movies2Go Video Library as I provide exhaustive testing that I have carried out to show that my database is exactly what is required by the client and that all the questions raised have been answered. ... Querying the database 2. ... Source Code for creating all tables /* VIDEO.SET Sets up all tables for the Videos-2-Go Video Library */ -- Drop existing tables and views DROP TABLE CARDHOLDER CASCADE CONSTRAINTS; DROP TABLE HOUSEHOLD_MEMBER CASCADE CONSTRAINTS; DROP TABLE MOVIE CASCADE CONSTRAINTS; DROP TABLE VIDEO_COPY CASCADE CONSTRAINTS; DROP TABLE VIDEO_LOAN CASCADE CONSTRAINTS; DROP TABLE MOVIE_TYPE CASCADE CONSTRAINTS; DROP VIEW AVAILABLE_MOVIES; DROP VIEW RENTED_MOVIES; /* TABLE CARDHOLDER Requirements: ------------- Each value of CardID is recorded only for one customer(requirement A), it is the primary key of this table and can only be a positive number. ... This table, like the rest of the database, only accepts text in upper case (requirement D) by CHECKing the case sensitive spelling of the input with the UPPERcase version of the same string. ... */ CREATE TABLE CARDHOLDER ( CardID NUMBER(3) CONSTRAINT C_POS_ID CHECK (CardID > 0) CONSTRAINT C_PK PRIMARY KEY, FName VARCHAR2(10) NOT NULL CONSTRAINT C_FName_Upper CHECK (FName = UPPER(FName)), LName VARCHAR2(10) NOT NULL CONSTRAINT C_LName_Upper CHECK (LName = UPPER(LName)), HouseNo NUMBER(3) CONSTRAINT C_POS_HNo CHECK (HouseNo > 0), Street VARCHAR2(16) CONSTRAINT C_Str_Upper CHECK (Street = UPPER(Street)), Town VARCHAR2(14) CONSTRAINT C_Town_Upper CHECK (Town = UPPER(Town)), PostCode VARCHAR2(8) NOT NULL CONSTRAINT C_PCode_Upper CHECK PostCode = UPPER(PostCode)), CONSTRAINT C_UNQ UNIQUE (FName, LName, PostCode) ); /* TABLE HOUSEHOLD_MEMBER Requirements: ------------- If a cardHolder is removed from the database, then details of any of their dependants is automatically deleted. ... A movie name can only be entered once to the database. ... Additional Requirements: ------------------------ A video Number has to be a positive number. The movie number cannot be null for a video. */ CREATE TABLE VIDEO_COPY ( VideoNo NUMBER(4) CONSTRAINT VC_Pos_VideoNo CHECK (VideoNo > 0) CONSTRAINT VC_PK PRIMARY KEY, MovieNo NUMBER(3) NOT NULL CONSTRAINT VC_MNo REFERENCES MOVIE (MovieNo) ); /* TABLE VIDEO_LOAN Requirements: ------------- When a video is borrowed, the issue date is automatically set to the current date by DEFAULTing it to SYSDATE (requirement E). ... SQL Source Code for inputing data in the tables /* Video.dat The following INSERT commands add rows to the tables created for the Videos2Go video library. ... Testing that all the specified requirements are met All these testings have been done by using INSERT, UPDATE and DELETE functions on the database obtained after inputing tha data given above. ... Testing that requirement A is satisfied R: Each value of CardID must be recorded for only one Cardholder Test: Lets try to insert another customer to the database with the CardID 1: INSERT INTO CARDHOLDER VALUES (1,ADAM,SMITH,22, CADOGAN STREET,LONDON,SW1 X4HT); Output: INSERT INTO CARDHOLDER * ERROR at line 1: ORA-00001: unique constraint (OPS$SERDARS. ... Testing that requirement B is satisfied R: The same Cardholder should not be able to be registered with identical details on the database and issued with a Borrower Card more than once; Test: Lets try to register Serdar Sokmen again: INSERT INTO CARDHOLDER VALUES (21,SERDAR,SOKMEN,56, COLEMAN ROAD,BRIGHTON,BN2 2SQ); Output: INSERT INTO CARDHOLDER * ERROR at line 1: ORA-00001: unique constraint (OPS$SERDARS. ... Testing that requirement D is satisfied R: The database must only accept text in upper case; Test: Lets try to insert a movie to the MOVIE table with the title in small letters INSERT INTO MOVIE VALUES (5,terminator,18,1); Output: INSERT INTO MOVIE * ERROR at line 1: ORA-02290: check constraint (OPS$SERDARS. ... Testing that requirement E is satisfied R: When a video is borrowed, the Date Borrowed field should be set automatically to todays date; Test: Lets insert a row to the VIDEO_LOAN TABLE without a borrowing date and then query the table to check the borrowing date. ... Testing that requirement G is satisfied R: If a Cardholder is removed from the database, then details of any of their dependants should automatically be deleted; Test: Lets try to delete David Beckham from the CARDHOLDER table. ... Additional requirement 7 R: A video Number has to be a positive number in the VIDEO_COPY table. ... Additional requirement 8 R: The movie number cannot be null for a video in the VIDEO_COPY table. ... M_CER_VALID) violated We have demonstrated in the above 11 points that the additional constraints that I have felt necessary to ensure the integity of the database do what they are supposed to do.