INF2603_202_2024.pdf

INF2603/202/0/2024 Tutorial letter 202/0/2024 Databases 1 INF2603 Year Module ASSIGNMENT 02 School of Computing IMPORTANT INFORMATION: This tutorial letter contains important information about your module. Assignment 02 Questions...

INF2603/202/0/2024 Tutorial letter 202/0/2024 Databases 1 INF2603 Year Module ASSIGNMENT 02 School of Computing IMPORTANT INFORMATION: This tutorial letter contains important information about your module. Assignment 02 Questions TOTAL MARKS: 100 Due date Tutorial matter covered in the prescribed book 27 September 2024 All chapters in the syllabus Unique number: 286114 QUESTION 1 [30 marks] a. What is redundancy? What problems are associated with redundancy? (4) b. Without using chartGP and examples from the book provide an example to emphasize your case, describe the situations when a 3rd NF relation is in BCNF and when it is not. (4) c. Describe what attributes represent in an ER model and provide examples of simple, composite, single-value, multi-value, and derived attributes. (6) d. Describe the relationship between a superclass and its subclass. (4) e. Describe and illustrate using an example the process of attribute inheritance. (4) f. How does multiplicity represent both the cardinality and the participation constraints on a relationship type? (4) g. Describe how fan occurs in an ER model and how it can be resolved. (4) QUESTION 2 [20 marks] a. Create an ER diagram to represent the data use by the library. The library provides books to borrowers. Each book is described by title, edition and year of publication and is uniquely identified using the ISBN. Each borrower is described by his or her name and address and is uniquely identified using a borrower number. The library provides one or more copies of each book and each copy is uniquely identified using a copy number, status indicating if the book is available for loan and the allowable loan period for a given copy. A borrower may loan one or many books and the date each book is loaned out and is returned is recorded. The loan number uniquely identifies each book loan. 2  INF2603/202/0/2024 QUESTION 3 [20 marks] a. The following exercise is based on the records of the company that stores information about their sessions (for Sports Physical Therapy). A session record will have a unique number, session date, patient number, length of the session, therapist number, and therapy code. Sports Physical Therapy. Determine the functional dependencies that exist in the following table, and then convert this table to an equivalent collection of tables that are in the 3rd normal form. (10) Session (SessionNum, SessionDate, PatientNum, LengthOfSession, TherapistID, TherapyCode, Description, UnitOfTime) b. Describe what is meant by transitive dependency and describe how this type of dependency relates to 3NF. Provide an example to illustrate your answer. (10) QUESTION 4 [30 marks] NOTE: All SQL syntax must be correct, missing characters will be penalized! BTEE Adventure Tours is a small business that organizes daylong, guided trips to South Africa. To support the company’s growing business, management uses a database to ensure that the company’s data is current, accurate, and easily accessible. The owner of Colonial Adventure Tours knows that being able to run queries is one of the most important benefits of using a DBMS. Use SQL commands to answer the following exercises, you will use the data in the Colonial Adventure Tours database shown in the below figures: Figure 4.1 presents sample trip data for BTEE Adventure Tours. Each trip is identified by a unique number called TripID. Furthermore, management tracks the trip name, the trip’s starting location, and the province in which the trip originates. In addition, management records the trip’s total distance (in KMs), the trip’s maximum group size, the trip’s type, and the season of the year. Figure 4.1: Trip TripID TripName StartLocation Province Distance MaxGrpSize Type Season 1 State Park Ride Maloti-Drakensberg Park KZN 15 8 Hiking Summer 2 Mount Mandela Boschkop Gauteng 3 8 Biking Early spring 3 Thaba Tshwaane Grootfontein Bike Park Gauteng 19 16 Biking Spring 4 Mount Zweli iSimangaliso Wetland Park KZN 75 4 Hiking Spring 5 God’s Window Hazeldean Valley Trails Gauteng 81 8 Biking Summer 6 Sharak Hills Underberg KZN 50 12 Hiking Late Spring 7 Waterfalls Paarl Western Cape 27 15 Paddling Early Fall 3 8 Njomane Mount Canoeing Mpumalanga 15 6 Paddling Summer 9 River Loop Rosemary Hill Farm Gauteng 104 18 Biking Spring 10 Swaart Kop Tierpoort Adventure Farm Gauteng 14 14 Biking Early Spring 11 Long Pond Velddrif Western cape 45 7 Paddling Late Spring 12 Mpuma Falls Kayaking Mpumalanga 38 14 Paddling Spring The table named Reservation shown in Figure 4.2 shows the sample of reservation data for BTEE Adventure Tours. Each reservation is identified by a unique reservation number that uses the last 2 digits of the current year followed by a three-digit number that increments sequentially. The table shows the TripID, date, number of persons, price per person, and other related information. Figure 4.2: Reservation ReseravtionID TripID TripDate NumPersons TripPrice OtherFees CustomerNum 24001 3 14/02/2024 12 R2250.00 R0.00 5501 24002 6 18/03/2024 10 R18000.00 R0.00 5501 24003 11 27/02/2024 5 R9000.00 R10.00 5526 24004 1 29/03/2024 4 R450.00 R15.00 5521 24005 4 10/04/2024 6 R19500.00 R0.00 5513 24006 9 19/04/2024 8 R45000.00 R25.00 5508 24007 8 03/03/2024 13 R1350.00 R12.00 5509 24008 7 21/02/2024 5 R1650.00 R35.00 5521 24009 10 25/04/2024 9 R1150.00 R35.00 5502 a. Write the SQL code to list the name of each trip that does not start in KZN. (3) b. Write the SQL code to list the name and start location for each trip that has the type Biking. (2) c. Write the SQL code to list the name of each trip that has the type Hiking and that has a distance greater than twenty kilometers. (4) d. Write the SQL code to list the name of each trip that has the type Paddling or that is located in Mpumalanga. (4) e. Write the SQL command that calculates how many trips have a type of Paddling or Biking. (6) f. Write the SQL code to list the trip name and province for each trip that occurs during the summer season. Sort the results by trip name within the same province. (4) g. How many reservations include a trip with a price that is greater than R2000.00 but less than R35000.00? (5) h. Write the SQL code to delete table RESERVATION from the database. (2) © UNISA 2024 4