Download Referential Integrity - Introduction to Databases - Exam Paper and more Exams Computer Science in PDF only on Docsity!
CORK INSTITUTE OF TECHNOLOGY
INSTITIÚID TEICNEOLAÍOCHTA CHORCAÍ
Autumn Examinations 2011
Module Title: Introduction to Databases – Continuous Assessment Module
Module Code: COMP
School: Science & Computing
Programme Title(s): Higher Certificate in Computing in Information Technology Support
Bachelor of Science (Honours) in IT Management
Programmes Code(s): CR_KITSU_6_Y
CR_KITMN_8_Y
External Examiner(s): Mr. Aidan Quilligan
Internal Examiner(s): Ms. Linda O’Sullivan
Instructions: Answer all 4 questions^ - all questions carry equal marks
Duration: 2 Hours
Sitting: Autumn 2011
Requirements for this examination:
Note to Candidates: Please check the Programme Title and the Module Title to ensure that you have received the correct examination. If in doubt please contact an Invigilator.
1.(a) Contrast each of the following terms: (i) Database; Database Management System (ii) Database application; Database System (5 Marks) (b) Compare and contrast the file-based and database approaches to handling data under the following headings: Program-Data Independence Application Development Time (5 Marks) (c) Read the following case study that describes the data requirements for an organisation called Booklovers Bookstores, a chain of bookstores, which sells books. You have been told that a book has exactly one publisher but a publisher may have published many books that Booklovers carries. However, Booklovers wants to be able to keep track of some publishers that currently have no books in Booklovers inventory as well. A book must have at least one author but can have more than one author. For an author to be of interest to Booklovers, he/she must have written at least one and possibly many books that Booklovers carries. A book may not yet have been purchased by any of its customers or it may have been bought by many of its customers. For a customer to be of interest to Booklovers, he/she must have purchased at least one book and possibly many. (i) Identify the main entity types for the company. (ii) Identify the main relationship types and specify the multiplicity for each relationship. State any assumptions you make about the data. (iii) Using your answers to (i) and (ii), draw a single ER diagram to represent the high level data requirements for Booklovers Bookstores, using either UML or Crow’s Foot notation. (15 marks)
- The following is a relational database schema for a used-car dealership: Car(Reg_No, Model, Manufacturer, Year, Engine, Colour, Mileage, Price) SalesRep(Rep_No, Name, Phone) Customer(Cust_No, Cust_Name, Address, Phone) Sale(Reg_No, Cust_No, Sale_Date, Sale_Price, Rep_No) where: Car contains details of used cars available for sale Salesrep contains details of the sales representatives working in the dealership Customer contains details of all customers those who have purchased cars together with those that haven’t Sale contains details of sales of cars to customers.
(a) Draw an Entity Relationship diagram for the above relational database schema, using either UML or Crow’s Foot notation, and clearly state any assumptions you have made.
- Based on the relational database schema given in question 2. Write SQL queries for the requests listed (a) – (e). (a) List full details of all cars registered in 2010, whose engine is at least 1.6L and costs between €20000 and €30000 – arrange the output in ascending order of engine size and within engine size in decreasing order of price. (5 Marks) (b) List all manufacturers for which there are more than 5 of their cars in stock. (5 Marks) (c) List full details of all sales representatives who have sold cars to a customer named Sarah Buckley who lives in Kinsale. (5 Marks) (d) For every car that has been sold, list full details of the car together with the name of the customer who bought the car. (5 Marks) (e) For each sale representative that has sold a car, list the sales representative’s number, name and the number of sales they have made together with the average value of their sales– arrange the output in descending order of the number of sales made.
