INF2603 Assignment 5 (DETAILED ANSWERS) 2025 - DISTINCTION GUARANTEED

INF2603 Assignment 5 (DETAILED ANSWERS) 2025 - DISTINCTION GUARANTEED - DISTINCTION GUARANTEED - DISTINCTION GUARANTEED Answers, guidelines, workings and references ,... 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. Because you are a registered student at UNISA and you have classmates who are your friends, please write SQL code to enter your own correct information and also the information of anther student into the STUDENT table. That is 2 records. For the student number of your friend you can use What is the difference between a column constraint and a table constraint? (What three join types are included in the OUTER JOIN classification? (6) What is a strong (or identifying) relationship? (2) Why is a table whose primary key consists of a single attribute automatically in 2NF when it is in 1NF? (2) 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. Explain what is a disjoint subtype? And give an example. (4) What three join types are included in the OUTER JOIN classification? (6) What is a strong (or identifying) relationship? (2) Why is a table whose primary key consists of a single attribute automatically in 2NF when it is in 1NF? (2) f. What is metadata? (2) g. Explain what is physical independence in data models? (2) Using the following transport business rules, draw the appropriate ERD using UML notation for a unique e-hailing taxi service in Mpumalanga. Make sure that you include all appropriate entities, relationships, and multiplicities: • One driver can be assigned to multiple taxis over time, but each taxi has only one driver at a time • Each customer can have multiple rides, and the e-hailing services offer sharing options for the customers • One taxi can be used for multiple rides • Each ride has one payment, and the customer can decide to cancel and the payment will be reversed Create an ER model for each of the following descriptions: • A large organization has several parking lots, which are used by staff. • Each parking lot has a unique name, location, capacity, and number of floors (where appropriate). • Each parking lot has parking spaces, which are uniquely identified using a space number. • Members of staff can request the sole use of a single parking space. Each member of staff has a unique number, name, telephone extension number, and vehicle license number. 4 • Represent all the ER models described in parts (a), (b), (c), and (d) as a single ER model. Provide any assumptions necessary to support your model. Make sure that you include all appropriate entities, relationships, and multiplicities. i. Write a SQL code to display the employees names, year of birth, and the bonus for each employee. Sort the results by the employee_id as in the table below. (5) Table 5.1: Employee_ID EmployeeNames_BirthYears Dept_ID Bonus Bontle Nxumalo (1982) 2305 Mpho Nkomo (1989) 2405 R24,000 Mahlako Nkomo (1984) 6605 Tshepi Phahlane (1986) 2305 R53,000 ii. Write the SQL code to delete the row for the employee who was paid R24,000 bonus. (2) 5.b. In the following questions, you will use the data of UNISA student registration information. i. Write the SQL code that will create the STUDENT table provided below. This table is a subset of the UNISA student registration database. (Note that the DEPT_CODE is the FK to DEPARTMENT, also use your own Data Declarations). (4) Table 5.2: The attributes of the Student Entity STUDENT StudentID {PK} DEPT_CODE {FK} STUD_Gender STUD_fname STUD_lname STUD_INITIAL STUD_EMAIL ii. Because you are a registered student at UNISA and you have classmates who are your friends, please write SQL code to enter your own correct information and also the information of anther student into the STUDENT table. That is 2 records. For the student number of your friend you can use . The Finance Department of SOUTHERN AFRICAN political party stores the Bonus field in the Employee table because a bonus is an attribute associated with employees. Given that information, use SQL commands to answer questions 4.a.i. i. Write the SQL code to delete the row for the employee who was paid R24,000 bonus from the below table Employee. (4) Employee_ID Dept_ID Bonus R24, R53,000 4.b. In the following questions, you will use the data of UNISA student registration information. i. Write the SQL code that will create the STUDENT table provided below. This table is a subset of the UNISA student registration database. (Note that the DEPT_CODE is the FK to DEPARTMENT, also use your own Data Declarations). (5) Table: The attributes of the Student Entity STUDENT StudentID {PK} DEPT_CODE {FK} STUD_Gender STUD_fname STUD_lname STUD_INITIAL STUD_EMAIL What is a strong (or identifying) relationship? (2) Why is a table whose primary key consists of a single attribute automatically in 2NF when it is in 1NF? (2) 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. Explain what is a disjoint subtype? And give an example. (4) What three join types are included in the OUTER JOIN classification? (6) What is a strong (or identifying) relationship? (2) Why is a table whose primary key consists of a single attribute automatically in 2NF when it is in 1NF? (2) f. What is metadata? (2) g. Explain what is physical independence in data models? (2) Using the following transport business rules, draw the appropriate ERD using UML notation for a unique e-hailing taxi service in Mpumalanga. Make sure that you include all appropriate entities, relationships, and multiplicities: • One driver can be assigned to multiple taxis over time, but each taxi has only one driver at a time • Each customer can have multiple rides, and the e-hailing services offer sharing options for the customers • One taxi can be used for multiple rides • Each ride has one payment, and the customer can decide to cancel and the payment will be reversed Create an ER model for each of the following descriptions: • A large organization has several parking lots, which are used by staff. • Each parking lot has a unique name, location, capacity, and number of floors (where appropriate). • Each parking lot has parking spaces, which are uniquely identified using a space number. • Members of staff can request the sole use of a single parking space. Each member of staff has a unique number, name, telephone extension number, and vehicle license number. 4 • Represent all the ER models described in parts (a), (b), (c), and (d) as a single ER model. Provide any assumptions necessary to support your model. Make sure that you include all appropriate entities, relationships, and multiplicities. i. Write a SQL code to display the employees names, year of birth, and the bonus for each employee. Sort the results by the employee_id as in the table below. (5) Table 5.1: Employee_ID EmployeeNames_BirthYears Dept_ID Bonus Bontle Nxumalo (1982) 2305 Mpho Nkomo (1989) 2405 R24,000 Mahlako Nkomo (1984) 6605 Tshepi Phahlane (1986) 2305 R53,000 ii. Write the SQL code to delete the row for the employee who was paid R24,000 bonus. (2) 5.b. In the following questions, you will use the data of UNISA student registration information. i. Write the SQL code that will create the STUDENT table provided below. This table is a subset of the UNISA student registration database. (Note that the DEPT_CODE is the FK to DEPARTMENT, also use your own Data Declarations). (4) Table 5.2: The attributes of the Student Entity STUDENT StudentID {PK} DEPT_CODE {FK} STUD_Gender STUD_fname STUD_lname STUD_INITIAL STUD_EMAIL ii. Because you are a registered student at UNISA and you have classmates who are your friends, please write SQL code to enter your own correct information and also the information of anther student into the STUDENT table. That is 2 records. For the student number of your friend you can use . The Finance Department of SOUTHERN AFRICAN political party stores the Bonus field in the Employee table because a bonus is an attribute associated with employees. Given that information, use SQL commands to answer questions 4.a.i. i. Write the SQL code to delete the row for the employee who was paid R24,000 bonus from the below table Employee. (4) Employee_ID Dept_ID Bonus R24, R53,000 ii. Write a SQL code to change your email address from your mylife email address to a Gmail address. (4) iii. Write a SQL code to delete the STUDENT table from the database. (2) 4.c. Write a series of SQL statements to insert the data shown in the below figure: (10) Hint: Use 24-hour clock times as in 14:00 for 2:00pm. Create an ER model for each of the following descriptions: • A large organization has several parking lots, which are used by staff. • Each parking lot has a unique name, location, capacity, and number of floors (where appropriate). • Each parking lot has parking spaces, which are uniquely identified using a space number. • Members of staff can request the sole use of a single parking space. Each member of staff has a unique number, name, telephone extension number, and vehicle license number. 4 • Represent all the ER models described in parts (a), (b), (c), and (d) as a single ER model. Provide any assumptions necessary to support your model. Make sure that you include all appropriate entities, relationships, and multiplicities. Open University of Zambia Course Enrolment Database OUniZa database stores information about students, courses, and enrolments. Initially, the data might be stored in a single table like this: University Table: StudentID StudentName CourseID CourseName Instructor EnrolmentDate 1 Thato 101 Math Dr. Nxumalo 2 Lucas 102 History Prof. Mtsweni 1 Thato 103 Physics Prof. Chipa 3 Sam 101 Math Dr. Nxumalo Given that information, answer the following questions: a. If possible, transfer the unnormalized form to first normal form. (4) b. Using the answer to problem a, remove existing partial dependencies and transform the relation into 2nd normal form. (6) c. Remove transitive dependencies and transform the relations from 2nd normal form to 3rd normal form. (6) a. 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) b. Describe and illustrate using an example the process of attribute inheritance. (4) c. What is the difference between a column constraint and a table constraint? (4) d. Explain what is a disjoint subtype? And give an example. (4) e. What is a strong (or identifying) relationship? (2) f. Why is a table whose primary key consists of a single attribute automatically in 2NF when it is in 1NF? (2) g. What is metadata? (2) h. Explain what is physical independence in data models? (2) 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. Because you are a registered student at UNISA and you have classmates who are your friends, please write SQL code to enter your own correct information and also the information of anther student into the STUDENT table. That is 2 records. For the student number of your friend you can use What is the difference between a column constraint and a table constraint? (What three join types are included in the OUTER JOIN classification? (6) What is a strong (or identifying) relationship? (2) Why is a table whose primary key consists of a single attribute automatically in 2NF when it is in 1NF? (2) 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. Explain what is a disjoint subtype? And give an example. (4) What three join types are included in the OUTER JOIN classification? (6) What is a strong (or identifying) relationship? (2) Why is a table whose primary key consists of a single attribute automatically in 2NF when it is in 1NF? (2) f. What is metadata? (2) g. Explain what is physical independence in data models? (2) Using the following transport business rules, draw the appropriate ERD using UML notation for a unique e-hailing taxi service in Mpumalanga. Make sure that you include all appropriate entities, relationships, and multiplicities: • One driver can be assigned to multiple taxis over time, but each taxi has only one driver at a time • Each customer can have multiple rides, and the e-hailing services offer sharing options for the customers • One taxi can be used for multiple rides • Each ride has one payment, and the customer can decide to cancel and the payment will be reversed Create an ER model for each of the following descriptions: • A large organization has several parking lots, which are used by staff. • Each parking lot has a unique name, location, capacity, and number of floors (where appropriate). • Each parking lot has parking spaces, which are uniquely identified using a space number. • Members of staff can request the sole use of a single parking space. Each member of staff has a unique number, name, telephone extension number, and vehicle license number. 4 • Represent all the ER models described in parts (a), (b), (c), and (d) as a single ER model. Provide any assumptions necessary to support your model. Make sure that you include all appropriate entities, relationships, and multiplicities. i. Write a SQL code to display the employees names, year of birth, and the bonus for each employee. Sort the results by the employee_id as in the table below. (5) Table 5.1: Employee_ID EmployeeNames_BirthYears Dept_ID Bonus Bontle Nxumalo (1982) 2305 Mpho Nkomo (1989) 2405 R24,000 Mahlako Nkomo (1984) 6605 Tshepi Phahlane (1986) 2305 R53,000 ii. Write the SQL code to delete the row for the employee who was paid R24,000 bonus. (2) 5.b. In the following questions, you will use the data of UNISA student registration information. i. Write the SQL code that will create the STUDENT table provided below. This table is a subset of the UNISA student registration database. (Note that the DEPT_CODE is the FK to DEPARTMENT, also use your own Data Declarations). (4) Table 5.2: The attributes of the Student Entity STUDENT StudentID {PK} DEPT_CODE {FK} STUD_Gender STUD_fname STUD_lname STUD_INITIAL STUD_EMAIL ii. Because you are a registered student at UNISA and you have classmates who are your friends, please write SQL code to enter your own correct information and also the information of anther student into the STUDENT table. That is 2 records. For the student number of your friend you can use . The Finance Department of SOUTHERN AFRICAN political party stores the Bonus field in the Employee table because a bonus is an attribute associated with employees. Given that information, use SQL commands to answer questions 4.a.i. i. Write the SQL code to delete the row for the employee who was paid R24,000 bonus from the below table Employee. (4) Employee_ID Dept_ID Bonus R24, R53,000 4.b. In the following questions, you will use the data of UNISA student registration information. i. Write the SQL code that will create the STUDENT table provided below. This table is a subset of the UNISA student registration database. (Note that the DEPT_CODE is the FK to DEPARTMENT, also use your own Data Declarations). (5)