UNIVERSITY OF FORT HARE [FS215 & IFS215E DATABASES JUNE 2023 Hours ‘Marks: 120 ‘This paper consists of SIX (6) pages including the cover page Internal Examiner Prof R Piderit Mr D Boucher Mr V Funda Dr E Chindenga GENERAL INSTRUCTIONS TO CANDIDATES, 1. This paper consists of four (4) SECTIONS. Answer TWO questions in SECTION A. Only the first two questions will be marked. Answer ALL the questions in SECTION B. Answer ALL the questions in SECTION C. Answer TWO questions in SECTION D. Answer in a new answer book. peoR 2. Questions can be answered in any order but must be numbered correctly. Any answers you do not want marked must be clearly cancelled. 3, Time management is very important. The value of the mark for each question should be used as a rough guide to the amount of time allocated to answer the question, (120 marks in 180 minutes) 4, Itis in the candidate's interest to write neatly. 5, At the end of the examination place all answer books inside the first answer book used. 6. Pencil may be used for the modelling questions. Page 1 of 6SECTION A [ANSWER TWO QUESTIONS FROM THIS SECTION] = 30 MARKS Question 1 [15 marks} a, There are nine (9) functions ofa database management system. Name and describe five of them, (5) b, Name and describe the three outcomes of data redundancy. © ¢. Consider a university registration system. One important business rule for this company might be that « student can register for a minimum of one modules and a maximum of ten modules, but each module must have a minimum of 20 students enrolled (with an unlimited maximum number). Describe and draw ‘his relationship using UML notation, © AND/OR Question 2 [15 marks] a. Describe five characteristics of a relational table, © b, Name and describe the five methods of classifying a database. © ©. Consider the PURCHASE and CUSTOMER tables below and provide the relational schema, (Note you will need to identify a suitable primary key for each table.] © Purchase table fea ae ana (03-22-2010 13, mam 8978 03-22-2010 ana 24323 8979 (03-22-2010 Son a 123 East 897 Banana 79 atieet so 1 400 aaa Alice a 9979 Watch 50 street Product table 24223 ‘Martha =. pe Customer table AND/OR Question 3 [15 marks} a, Identify and describe five characteristies of data quality. 6 b. Describe (with examples) the difference between a subset, complement and intersection. ©) Use the following to answer (¢) and (@): T= (15253;43556;7;859:10} I= {3345536} N= £13253} IV = (11512313) ¢. Do land IV have any common members? Write your answer in the proper notation, @ 4. IfT want to join Il and IIT (make them a union), how would I show this with proper notation? O) [TURN OVER THE PAGE FOR NEXT SECTION] Page 2 of 6SECTION B [ANSWER ALL THE QUESTIONS FROM THIS SECTION Question 4 [10 marks} Data modelling is the first step in the database design journey, serving as a bridge between real-world objects and the database that resides in the computer. a. What makes data modelling important? ) b. What is a business rule, and what i its purpose in data modelling? © Question 5 [20 marks} Scenario: A library system Andile has been asked by his boss to draw the Entity Relationship Diagram (ERD) to set up a new database for Library System. He has managed to find some of the information he needs below from interviewing a few people who work for the library. But he knows he is missing information about Primary and Foreign Keys. He is worried as his boss said this isa big elient and his job is “on the line”, Andile has collected the following information - The library has multiple books, each with a unique title and author(s). Each book can have multiple copies, and each copy has a unique ID. The library also has multiple members who can borrow books. Andile has collected the following entity information: ‘© Book (with attributes: title and author) © Copy (with attributes: ID and availability status) © Member (with attributes: name, email, and phone number) He also thinks he knows what the relationships are between these entities ‘+ A book can have multiple copies ‘+ Acopy can be borrowed by one member ata time * A member can borrow multiple copies at different times Help Andile keep his job by assisting him with creating an ERD using UML notation and indicate all primary and foreign keys. [TURN OVER THE PAGE FOR NEXT SECTION] Page 3 of 6SECTION C [ANSWER ALL OF THE QUESTIONS FROM THIS SECTION] = 30 MARKS Question 6 [10 marks] Having good relational database software is not enough to avoid the data redundancy. a. Provide a brief definition of normalisation and describe the purpose of normalisation. 6) 'b. Describe the processisteps to convert a table to 2NF. (6) AND Question 7 (20 marks} Sample Value | Sample Value | Sample Value _| Sample Velue STUN | 21138 200328 138876 153876, zs WV \y Fstuctnaie —[stepnaros | Sou [venes ‘one tekst \ \sstunaion | pecouting | recouting | Warroing | stcing | Satter WW \, ) | bert cove [poor ‘Acct [ats HATS ar } Veer wine 7Taecouning | Accourtng | Waning | Mavitng | Matheraes f DEPT. PHONE VY 356 4366 4378 78 3420, COLLEGE NAME J Bunnons Aiman | Busnes Adria | Businass Aarin_| Bsnons Aoran_| Ate & Sconces ‘AOMSOR | Gaseend | Gaston | Gantry Tey ren Stun | NA [anus oF aor Te8 Se ar ICE | ABVSOR-BLGG | Tove Bulag | Tore Bulag | Tore Buldng | Tore Bundng | Jones Buldg \|awson 2115 ae 7s 798 3209 PONE _ Using the STUDENT table structure shown above: a, Write the relational schema. a 'b, Draw its dependency diagram, and identify all dependencies (including all partial and transitive dependencies). You can assume that the table does not contain repeating groups. © c. Using the answer to (b), remove all partial dependencies, write the relational schema and draw the new dependency diagrams. © 4. Identify the normal forms for each table structure you created in (c).. @ [TURN OVER THE PAGE FOR NEXT SECTION] Page 4 of 6|ANSWER THIS SECTION IN A NEW BOOK SECTION D [ANSWER TWO QUESTIONS FROM THIS SECTION] = 30 MARKS Question 8 [15 marks} Study the SQL query below and answer the following questions SELECT DISTINCT CustomerName, CustomerEmail, CustomerTelephone, Sum (Quantity * ProductsPrice) as Total_spent FROM Orders JOIN Customers USING(Customer_id) JOIN Order_items USING(Order_id) JOIN Products USING(Product_id) WHERE Order_date = '2023-05-15' AND Order_date < '2623-05-16" GROUP BY Customer_id, CustomerName, CustomerEmail ORDER BY total_spent DESC. a. Identify the table(s) that will be joined in this query. @ b, Explain the purpose of the DISTINCT function in the given SQL statement, @ ¢. State and explain if this query is an example of a data definition or data manipulation language? (2) 4. Explain the purpose of the ORDER BY clause in this SQL query. @ ¢. Identify and list all the attributes from the CUSTOMER table that will be used in this query? @) £ How does the ORDER BY clause affect the results of the query shown above? 2 AND/OR, Question 9 [15 marks] ‘a. How does the UNION operator differ from the JOIN operator in SQL, and when should you use the UNION operator? @ bb. State and explain two benefits of Stored Procedures in SQL. +2) c. What does the acronym DBLC mean, and what does a DBLC portray? @ 4. Explain the concept of a deadlock in a database system? @ eHow can backup and recovery procedures contribute to database security? e@ £. Explain the concept of Transaction Isolation in a multi-user database environment. @ AND/OR, [TURN OVER THE PAGE FOR NEXT QUESTION] Page 5 of 6Question 10 [15 marks} Study the ERD below and answer the following questions. # (eo Coste aes) a. Write the SQL query to create a database named MyFarm_StudentNumber. Replace StudentNumber with your university student number, 2 b, Write SQL queries to create the Farm Table and the Crop Table from Database Schema shown above. +4) &. Write a SQL query that can be used to add a record to the Animal Table based on the Database Schema shown above. @ d. Write a SQL query to calculate the sum of the Quantity column from the Crop table and retum it as a single row with a column name of TotalCropQuantity. @ e. What is the purpose of the foreign key relationship between the Animal and Farm tables? @ [END OF PAPER] Page 6 of 6