Examinations for 2016 - utm.ac.mu

1 Page 1 of 10 DATABASE DESIGN (DBT1111C) SITE/Dec 2016/2017 Sem 1 BSc (Hons) Computer Science with Network Security Cohort: BCNS/16B/FT Examinations for 2016 - 2017 / Semester 1 Resit Examinations for BCNS/14B/FT MODULE: DATABASE DESIGN MODULE CODE: DBT 1111C Duration: 3 Hours Instructions to Candidates: 1. Answer ALL FOUR questions. 2. Questions may be answered in any order but your answers must show the question number clearly.

2 3. Always start a new question on a fresh page. 4. All questions carry equal marks. 5. Total marks 100. This Examination paper contains 4 questions and 10 pages. Page 2 of 10 DATABASE DESIGN (DBT1111C) SITE/Dec 2016/2017 Sem 1 ANSWER ALL FOUR QUESTIONS QUESTION 1: (25 MARKS) (a) Describe briefly the following Database Languages: (i) Data Manipulation Language (DML) (ii) Data Definition Language (DDL) (iii) Host Language (iv) Query Language [8 marks] (b) Define the following terms (i) Tuple (ii) Degree of a relation (iii) Cardinality of a relation (iv) Primary Key (v) Header Support your answer with examples.

3 [5 marks] (c) With the help of appropriate example describe briefly the following terms on attributes: [4 marks] (i) Simple attribute (ii) Composite attribute (iii) Multivalued attribute (iv) Derived attribute (d) Describe briefly any four essential roles played by a DBA (Database Administrator). [8 Marks] Page 3 of 10 DATABASE DESIGN (DBT1111C) SITE/Dec 2016/2017 Sem 1 QUESTION 2: (25 MARKS) The Curepipe Turf Club (CTC) wishes to develop an MIS to keep track of horse races organized (over 15 weeks week 1 to week 15) every weekend in Curepipe.

4 You have been asked to develop a high level data model based on the following findings. A horse stable can have many horses and each horse belongs to one stable only. A horse stable has one owner only and one owner can possess only one horse stable. A horse stable can have one or more horse coaches and jockeys. A coach can train one or more horses but each horse is trained by one coach only. A jockey may ride one or more horses with a maximum of five (5) horses but one at a time. Each week consists of eight tracks (Track 1 to Track 8) with one among them being a special track (sponsored by a renown private company but the latter are not recorded in the system for the time being).

5 Each week a title is given to the special track for the Air Mauritius Maiden Cup . A horse may run in up to three (3) tracks in the same week or may not run at all. A track is organized if the number of horses running is between four (4) and eight (8) otherwise it is cancelled. (a) Produce an Entity Relationship Diagram (Logical Data Structure) for the above scenario. For each entity, display two non - key attributes which you think are the most important and identify their respective primary key(Underline). (7 marks for entities and attributes, 4 marks for correct relationships, 2 marks for cardinality constraints: Total 13 marks) [13 marks] (b) Translate your ERD into a relational database schema [12 marks] Page 4 of 10 DATABASE DESIGN (DBT1111C) SITE/Dec 2016/2017 Sem 1 QUESTION 3: (25 MARKS) Using the tables provided in the Appendix A, express the following queries in SQL USING ORACLE 9i/10g/11g SYNTAX.

6 (a) Write the SQL code that will create the table structure for the Player table (all fields being mandatory) and (without specifying any primary or foreign keys). [4 marks] (b) Add a PRIMARY KEY constraint to make the p_Id the primary key of the Player table [2 marks] (c) Assume that you had already created the table Player_Club (P_Cl) but you forgot to add a column for the foreign key. Write SQL code to add a column with the following data definition (Column Name P_Id of Varchar data type and field size of 3, contains a foreign key constraint and that data cannot be NULL) for the latter.

7 [4 marks] (d) Having created the table Player in question (a) above, write the SQL code that will enter the first two data rows into the Player table. [4 marks] (e) Create a view (view name as well as column header is to be Highest_Capacity) for the following query: to display the stadium name, with the highest capacity. [6 marks] (f) Write the SQL code to delete the player with First name Gopaul and Date of birth (DOB) is 05-23-1998 from the player table. [2 marks] (g) The address 2 (Add2) of the player Ramen Marday in the Player table has been recently changed to Rose Hill.

8 Write SQL code to update the address of that player. [3 marks] Page 5 of 10 DATABASE DESIGN (DBT1111C) SITE/Dec 2016/2017 Sem 1 QUESTION 4: (25 MARKS) This question refers to the database tables given in the Appendix A. Formulate each of the following queries in Relational Algebra. (a) Display the last names of Players who played in the club Liverpool between 6/20/2012 and 3/24/2014 (mm/dd/yyyy format) [4 marks] (b) Display the first names of all players who played in either Man U or Chelsea . [4 marks] (c) List the last names of coaches who have coached in all clubs.

9 [6 marks] (d) Display the last names of coaches who have not coached in the club Barcelone . [7 marks] (e) Display the names of the players who live in the same city as their club where they are actually playing. [4 marks] Page 6 of 10 DATABASE DESIGN (DBT1111C) SITE/Dec 2016/2017 Sem 1 APPENDIX A The tables and relationship diagram provided in appendix A are to be used for both Question 3 and Question 4 Table Player Page 7 of 10 DATABASE DESIGN (DBT1111C) SITE/Dec 2016/2017 Sem 1 Table Club Table Coach Page 8 of 10 DATABASE DESIGN (DBT1111C) SITE/Dec 2016/2017 Sem 1 Table Stadium Table Coach Club Page 9 of 10 DATABASE DESIGN (DBT1111C)

10 SITE/Dec 2016/2017 Sem 1 Table Player Club Table Stadium Club The tables above show sample data (The data are only indicative. Students should not rely on this data visually to answer their questions!). Page 10 of 10 DATABASE DESIGN (DBT1111C) SITE/Dec 2016/2017 Sem 1 Table details: Player: P_Id is Player Id, PFName is Player First Name, PLName is Player Last Name, Add1 is Player address 1, Add2 is Player address 2, Phone is Player phone, DOB is player Date of birth, Level is the level at which player is currently playing, Cl_Id is Club Id.