System and Databases - Assignment Example

1. Use the data items provided to produce a full set of normalised relations for the case study. Extra data items may be added providing they are defined and their purpose explained. Solution: Before creating tables (relations) in a database, it is important to design an ERD (Entity Relationship Diagram) in order to identify the relationship between entities in a database. Therefore, I have designed an ERD using Crow’s Foot notation for the given scenario of Serene Screens Cinema Group as shown below: Keeping the above shown ER diagram in focus, I have created the following tables (relations) in a database: a. CINEMA(CINEMA_ID, LOCATION) SQL Script: CREATE TABLE "CINEMA" ( "CINEMA_ID" NUMBER NOT NULL ENABLE, "LOCATION" VARCHAR2(150) NOT NULL ENABLE, CONSTRAINT "CINEMA_PK" PRIMARY KEY ("CINEMA_ID") ENABLE ) b. CUSTOMER(CUST_ID, FIRST_NAME, LAST_NAME, ADDRESS, EMAIL, TELE_NO) SQL Script: CREATE TABLE "CUSTOMER" ( "CUST_ID" NUMBER NOT NULL ENABLE, "FIRST_NAME" VARCHAR2(50) NOT NULL ENABLE, "LAST_NAME" VARCHAR2(50) NOT NULL ENABLE, "ADDRESS" VARCHAR2(150) NOT NULL ENABLE, "EMAIL" VARCHAR2(100) NOT NULL ENABLE, "TELE_NO" VARCHAR2(50) NOT NULL ENABLE, CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUST_ID") ENABLE ) c. FILM(FILM_ID, TITLE) SQL Script: CREATE TABLE "FILM" ( "FILM_ID" NUMBER NOT NULL ENABLE, "TITLE" VARCHAR2(100) NOT NULL ENABLE, CONSTRAINT "FILM_PK" PRIMARY KEY ("FILM_ID") ENABLE ) d. HOLIDAY(HOLIDAY_ID, START_DATE, END_DATE, STAFF_ID) SQL Script: CREATE TABLE "HOLIDAY" ( "HOLIDAY_ID" NUMBER NOT NULL ENABLE, "START_DATE" DATE NOT NULL ENABLE, "END_DATE" DATE NOT NULL ENABLE, "STAFF_ID" NUMBER NOT NULL ENABLE, CONSTRAINT "HOLIDAY_PK" PRIMARY KEY ("HOLIDAY_ID") ENABLE, CONSTRAINT "STAFF_ID_HOLIDAY_FK" FOREIGN KEY ("STAFF_ID") REFERENCES "STAFF" ("STAFF_ID") ENABLE ) e. KIOSK_SALE(SALE_DATE, UNIT_SOLD, TOTAL_PRICE, PRODUCT_ID, CINEMA_ID) SQL Script: CREATE TABLE "KIOSK_SALE" ( "SALE_DATE" DATE NOT NULL ENABLE, "UNIT_SOLD" NUMBER NOT NULL ENABLE, "TOTAL_PRICE" NUMBER NOT NULL ENABLE, "PRODUCT_ID" NUMBER NOT NULL ENABLE, "CINEMA_ID" NUMBER NOT NULL ENABLE, CONSTRAINT "PRODUCT_ID_KIOSK_SALE_FK" FOREIGN KEY ("PRODUCT_ID") REFERENCES "PRODUCT" ("PRODUCT_ID") ENABLE, CONSTRAINT "CINEMA_ID_KIOSK_SALE_FK" FOREIGN KEY ("CINEMA_ID") REFERENCES "CINEMA" ("CINEMA_ID") ENABLE ) f. ORDER_DETAILS(PROD_ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY, SUPPLIER_ID) SQL Script: CREATE TABLE "ORDER_DETAILS" ( "PROD_ORDER_ID" NUMBER NOT NULL ENABLE, "PRODUCT_ID" NUMBER NOT NULL ENABLE, "UNIT_PRICE" NUMBER NOT NULL ENABLE, "QUANTITY" NUMBER NOT NULL ENABLE, "SUPPLIER_ID" NUMBER NOT NULL ENABLE, CONSTRAINT "ORDER_DETAILS_PK" PRIMARY KEY ("PROD_ORDER_ID", "PRODUCT_ID") ENABLE, CONSTRAINT "PROD_ORDER_DETAILS_FK" FOREIGN KEY ("PROD_ORDER_ID") REFERENCES "PROD_ORDER" ("PROD_ORDER_ID") ENABLE, CONSTRAINT "PRODUCT_ID_ORDER_DETAILS_FK" FOREIGN KEY ("PRODUCT_ID") REFERENCES "PRODUCT" ("PRODUCT_ID") ENABLE, CONSTRAINT "SUPPLIER_ID_ORDER_DETAILS_FK" FOREIGN KEY ("SUPPLIER_ID") REFERENCES "SUPPLIER" ("SUPPLIER_ID") ENABLE ) g. PAY_METHOD(PAY_METHOD_ID, DESCRIPTION) SQL Script: CREATE TABLE "PAY_METHOD" ( "PAY_METHOD_ID" NUMBER NOT NULL ENABLE, "DESCRIPTION" VARCHAR2(100) NOT NULL ENABLE, CONSTRAINT "PAY_METHOD_PK" PRIMARY KEY ("PAY_METHOD_ID") ENABLE ) h. PRODUCT(PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE, UNITS_IN_STOCK) SQL Script: CREATE TABLE "PRODUCT" ( "PRODUCT_ID" NUMBER NOT NULL ENABLE, "PRODUCT_NAME" VARCHAR2(100) NOT NULL ENABLE, "UNIT_PRICE" NUMBER NOT NULL ENABLE, "UNITS_IN_STOCK" NUMBER NOT NULL ENABLE, CONSTRAINT "PRODUCT_PK" PRIMARY KEY ("PRODUCT_ID") ENABLE ) i. PROD_ORDER(PROD_ORDER_ID, REQUEST_DATE, RECEIVE_DATE, PAY_DATE) SQL Script: CREATE TABLE "PROD_ORDER" ( "PROD_ORDER_ID" NUMBER NOT NULL ENABLE, "REQUEST_DATE" DATE NOT NULL ENABLE, "RECIEVE_DATE" DATE, "PAY_DATE" DATE, CONSTRAINT "PROD_ORDER_PK" PRIMARY KEY ("PROD_ORDER_ID") ENABLE ) j. PROGRAMME(PROGRAMME_ID, PROGRAMME_DATE, START_TIME, END_TIME, SPECIAL, FILM_ID, CINEMA_ID) SQL Script: CREATE TABLE "PROGRAMME" ( "PROGRAMME_ID" NUMBER NOT NULL ENABLE, "PROGRAMME_DATE" DATE NOT NULL ENABLE, "START_TIME" TIMESTAMP (6) NOT NULL ENABLE, "END_TIME" TIMESTAMP (6) NOT NULL ENABLE, "SPECIAL" CHAR(1) DEFAULT N NOT NULL ENABLE, "FILM_ID" NUMBER NOT NULL ENABLE, "CINEMA_ID" NUMBER NOT NULL ENABLE, CONSTRAINT "PROGRAMME_PK" PRIMARY KEY ("PROGRAMME_ID") ENABLE, CONSTRAINT "FILM_ID_PROGRAMME_FK" FOREIGN KEY ("FILM_ID") REFERENCES "FILM" ("FILM_ID") ENABLE, CONSTRAINT "CINEMA_ID_PROGRAMME_FK" FOREIGN KEY ("CINEMA_ID") REFERENCES "CINEMA" ("CINEMA_ID") ENABLE ) k. ROTA(ROTA_ID, ROTA_DATE, START_TIME, END_TIME) SQL Script: CREATE TABLE "ROTA" ( "ROTA_ID" NUMBER NOT NULL ENABLE, "ROTA_DATE" DATE NOT NULL ENABLE, "START_TIME" TIMESTAMP (6) NOT NULL ENABLE, "END_TIME" TIMESTAMP (6) NOT NULL ENABLE, CONSTRAINT "ROTA_PK" PRIMARY KEY ("ROTA_ID") ENABLE ) l. SALARY(SALARY_ID, STAFF_ID, START_DATE, END_DATE, TOTAL_PAY) SQL Script: CREATE TABLE "SALARY" ( "SALARY_ID" NUMBER NOT NULL ENABLE, "STAFF_ID" NUMBER NOT NULL ENABLE, "START_DATE" DATE NOT NULL ENABLE, "END_DATE" DATE NOT NULL ENABLE, "TOTAL_PAY" NUMBER NOT NULL ENABLE, CONSTRAINT "SALARY_PK" PRIMARY KEY ("SALARY_ID", "STAFF_ID") ENABLE, CONSTRAINT "STAFF_ID_SALARY_FK" FOREIGN KEY ("STAFF_ID") REFERENCES "STAFF" ("STAFF_ID") ENABLE ) m. STAFF(STAFF_ID, FIRST_NAME, LAST_NAME, ADDRESS, TELE_NO, HOURLY_RATE) SQL Script: CREATE TABLE "STAFF" ( "STAFF_ID" NUMBER NOT NULL ENABLE, "FIRST_NAME" VARCHAR2(50) NOT NULL ENABLE, "LAST_NAME" VARCHAR2(50) NOT NULL ENABLE, "ADDRESS" VARCHAR2(150) NOT NULL ENABLE, "TELE_NO" VARCHAR2(50) NOT NULL ENABLE, "HOURLY_RATE" NUMBER NOT NULL ENABLE, CONSTRAINT "STAFF_PK" PRIMARY KEY ("STAFF_ID") ENABLE, CONSTRAINT "STAFF_NAME_UK" UNIQUE ("FIRST_NAME", "LAST_NAME") ENABLE ) n. STAFF_ROTA(STAFF_ID, ROTA_ID, WORK_TYPE_ID, ATTENDANCE, TOTAL_PAY) SQL Script: CREATE TABLE "STAFF_ROTA" ( "STAFF_ID" NUMBER NOT NULL ENABLE, "ROTA_ID" NUMBER NOT NULL ENABLE, "WORK_TYPE_ID" NUMBER NOT NULL ENABLE, "ATTENDANCE" CHAR(1) DEFAULT N NOT NULL ENABLE, "TOTAL_PAY" NUMBER NOT NULL ENABLE, CONSTRAINT "STAFF_ROTA_PK" UNIQUE ("STAFF_ID", "ROTA_ID") ENABLE, CONSTRAINT "STAFF_ID_STAFF_ROTA_FK" FOREIGN KEY ("STAFF_ID") REFERENCES "STAFF" ("STAFF_ID") ENABLE, CONSTRAINT "ROTA_ID_STAFF_ROTA_FK" FOREIGN KEY ("ROTA_ID") REFERENCES "ROTA" ("ROTA_ID") ENABLE, CONSTRAINT "WORK_TYPE_ID_STAFF_ROTA_FK" FOREIGN KEY ("WORK_TYPE_ID") REFERENCES "WORK_TYPE" ("WORK_TYPE_ID") ENABLE ) o. SUPPLIER(SUPPLIER_ID, SUPPLIER_NAME, ADDRESS, EMAIL, TELE_NO) SQL Script: CREATE TABLE "SUPPLIER" ( "SUPPLIER_ID" NUMBER NOT NULL ENABLE, "SUPPLIER_NAME" VARCHAR2(100) NOT NULL ENABLE, "ADDRESS" VARCHAR2(150) NOT NULL ENABLE, "EMAIL" VARCHAR2(100) NOT NULL ENABLE, "TELE_NO" VARCHAR2(50) NOT NULL ENABLE, CONSTRAINT "SUPPLIER_PK" PRIMARY KEY ("SUPPLIER_ID") ENABLE ) p. TICKET(TICKET_ID, PURCHASE_DATE, PRICE, PROGRAMME_ID, CUST_ID, PAY_METHOD_ID) SQL Script: CREATE TABLE "TICKET" ( "TICKET_ID" NUMBER NOT NULL ENABLE, "PURCHASE_DATE" DATE NOT NULL ENABLE, "PRICE" NUMBER NOT NULL ENABLE, "PROGRAMME_ID" NUMBER NOT NULL ENABLE, "CUST_ID" NUMBER NOT NULL ENABLE, "PAY_METHOD_ID" NUMBER, CONSTRAINT "TICKET_PK" PRIMARY KEY ("TICKET_ID") ENABLE, CONSTRAINT "PROGRAMME_ID_TICKET_FK" FOREIGN KEY ("PROGRAMME_ID") REFERENCES "PROGRAMME" ("PROGRAMME_ID") ENABLE, CONSTRAINT "CUST_ID_TICKET_FK" FOREIGN KEY ("CUST_ID") REFERENCES "CUSTOMER" ("CUST_ID") ENABLE ) q. WORK_TYPE(WORK_TYPE_ID, DESCRIPTION) SQL Script: CREATE TABLE "WORK_TYPE" ( "WORK_TYPE_ID" NUMBER NOT NULL ENABLE, "DESCRIPTION" VARCHAR2(100) NOT NULL ENABLE, CONSTRAINT "WORK_TYPE_PK" PRIMARY KEY ("WORK_TYPE_ID") ENABLE, CONSTRAINT "WORK_TYPE_DESC_UK" UNIQUE ("DESCRIPTION") ENABLE ) 2. Draw up a retrievals catalogue for the case study giving. For each retrieval, the purpose of the retrieval, the tables used and the output from it. Solution: S. No. Purpose of retrieval Tables used Output 1. To list the film viewing figures for the specified period. 2. To show the details of the customer complaints for the specified period. 3. To show the details of rotas for the specified period. 4. To show the details of holidays for the particular staff. 5. To show the details of wages for the particular staff. 6. To show the details of sold tickets for the specified period. 7. To show the details of sold kiosks for the specified period. 3. Use appropriate SQL features to implement a subset of the retrievals catalogue, concentrating on a few complex functions rather than much simple and repetitive retrieval. The retrievals implemented should also be of obvious business relevance to the organisation in the case study. Solution: Query 1: To list films in alphabetical order with the total number of tickets sold and the revenue generated against those tickets during the specified period (from 1st Aug, 2009 till 10th Aug, 2009 in this case). SELECT F.TITLE, TO_CHAR(COUNT(*), 99,999,999,999,999) "TOTAL TICKETS SOLD ", (£ || TO_CHAR(SUM(T.PRICE), 999,999,999,999,999.99)) "TOTAL REVENUE" FROM FILM F, TICKET T, PROGRAMME P WHERE F.FILM_ID = P.FILM_ID AND P.PROGRAMME_ID = T.PROGRAMME_ID AND T.DATE_OF_PURCHASE BETWEEN 01-AUG-09 AND 10-AUG-09 GROUP BY F.TITLE; ORDER BY F.TITLE; Output: Query 2: To list top ‘N’ (1 in this case) customers who purchased highest number of tickets during the specified period (from 1st Aug, 2009 till 31st Aug, 2009 in this case). SELECT * FROM( SELECT (C.FIRST_NAME || || C.LAST_NAME) "CUSTOMER NAME", C.TELE_NO "CONTACT NUMBER", C.EMAIL "EMAIL ADDRESS", TO_CHAR(COUNT(*), 99,999,999,999,999) "TICKETS PURCHASED", (£ || TO_CHAR(SUM(T.PRICE), 999,999,999,999,999.99)) "REVENUE GENERATED" FROM CUSTOMER C, TICKET T WHERE C.CUST_ID = T.CUST_ID AND T.DATE_OF_PURCHASE BETWEEN 01-AUG-09 AND 31-AUG-09 GROUP BY C.FIRST_NAME, C.LAST_NAME, C.TELE_NO, C.EMAIL ORDER BY COUNT(*) DESC ) DUMMY WHERE ROWNUM Read More