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. ...
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.
To link to this page, copy the following code to your site:
All Papers Are For Research And Reference Purposes Only!
You may not turn these papers in as your own! You must cite our web site as your source!
