Download Database Management - 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 2008/
Module Title: Introduction to Databases (C/A)
Module Code: COMP 6016
School: Computing & Mathematics
Programme Title: Higher Certificate in Computing in Information Technology Support
Programme Code: KITSU_6_Y
External Examiner(s): Mr K Carroll Internal Examiner(s): Ms L O Sullivan
Instructions: Answer all FOUR questions. All questions carry equal marks.
Duration: 2 Hours
Sitting: Autumn 2009
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 paper. If in doubt please contact an Invigilator.
1.(a) Explain what is meant by each of the following terms: (i) File-based system (ii) Database (iii) Database Management System (DBMS) (iv) Database Application (v) Database System
Each of the above terms can be defined as follows: (5 marks) (b) Describe the approach taken to the handling of data in the early file-based systems. Discuss two disadvantages of this approach. (5 marks) (c) You are required to create a conceptual data model of the data requirements for a company that specializes in IT training. The Company has 30 instructors and can handle up to 100 trainees per training session. The Company offers five advanced technology courses, each of which is taught by a teaching team of two or more instructors. Each instructor is assigned to a maximum of two teaching teams or may be assigned to do research. Each trainee undertakes one advanced technology course per training session.
(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. (iii)Using your answers for (i) and (ii), draw a single ER diagram to represent the data requirements for the company. (15 marks)
- Using the relational database schema given in question 2, and keeping the following in mind, answer questions (a) to (e).
- Slsrep o Rep_No, Rep_Name, Rep_Addr are required fields. o Rep_Addr is unique, i.e. one and only one sales representative lives at a given address o Tot_Comm must be in the range of €0 to €10000, with a default of 0 o Comm_Rate must not exceed 0.5, i.e. 50%, with a default of 0.01.
- Customer o Cust_no, Cust_Name and Rep_No are required fields. o Curr_Bal should have a default value of zero and must never be less than 0. o Credit must be a €1000 or less, with a default of 100. (a) Write the SQL statements to create the Slsrep and Customer relations, including appropriate versions of all primary and foreign key constraints, as well as stating whether a column is required or not and its default value where applicable. (10 marks)
(b) Write a SQL statement to add Timothy Barry, of 17 The Oaks, Wilton, Cork, as a sales representative with a Rep_No of 7. You must ensure that such a record can be inserted based on your answer to part (a) above. (2 marks)
(c) Write a SQL statement to set the current balance to zero of all customers managed by a sales representative called Sam Brown. (4 marks)
(d) Write a SQL statement to delete all sales representatives that have no customers assigned to them. (4 marks)
(e) Write a SQL statement to change the Rep_No of Rep_No 7 to 10.. Given the referential integrity constraints you chose for this schema, explain what happens when this statement is executed. (5 marks)
- 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 parts whose part number begins with the letter D and whose unit price is in the range of €100 to €500 - in alphabetical order of class and within class the most expensive part should be listed first and the least expensive part should be listed last. (5 marks)
(b) Find out how many customers are located in Cork, their maximum credit rating and their average customer balance. (5 marks)
(c) For each sales representative that manages two or more customers, list the sales representative’s number, the number of customers they manage and the total current balance of the customers they manage. (5 marks)
(d) List the number and name of all sales representatives who manage 2 or more customers. (5 marks) (e) For each order, list the order number, order date, the number of different parts ordered and the total value of the order^1. (5 marks)
(^1) The value of a particular order line is its quantity ordered by its quantity price.
