CSCI235
Assignment 1 (10% of total marks)
Due date: Tuesday, 22 October 2024 by 9:00 pm SGT. Scope:
The tasks of this assignment consist of problems related to functional dependency and normalization. The assignment covers the topics discussed in lectures 1 and 2.
Assessment criteria:
Marks will be awarded for:
• Correct,
• Comprehensive, and
• Appropriate
application of the materials covered in this subject.
Assignment Specification:
Task 1 (4.0 marks)
Analysis of conceptual schemas and normalization
Consider the following conceptual schema of a sample database domain that contains information about job application.
i. Identify all functional dependencies and multivalued dependencies present in the job application’s conceptual schema.
ii. Determine all minimal keys for the relational tables derived from the conceptual schema. Provide detailed derivations for each minimal key.
iii. For each relational schema, identify the highest normal form (HNF) it satisfies. Justify why each schema reaches that specific normal form.
iv. For any relational schemas that are not in 4NF, decompose them into 4NF. List all schemas resulting from these decompositions, and clearly indicate the primary key and any foreign keys.
Deliverables
A file solution1.pdf with the outcomes of the steps (i), (ii), (iii), and (iv) listed above. Note, that "educated guesses" of the solutions score no marks. You must provide the complete justifications of your answers.
Task 2 (3.0 marks)
Analysis of relational schemas and normalization
Consider the following unnormalized relational table PROJECT:
PROJECT (ProjectTitle, EmpName, ManagerName, Location, HoursWork, ManagerPhone)
The attributes of the PROJECT table satisfy the following conditions:
• Each project can have multiple employees.
• Each employee may be involved in one or more projects.
• Projects are managed by managers. There are many managers in the company, and each project is assigned to a single manager. However, each manager manages only one project.
• Each project is associated with a specific location.
• The number of hours an employee works on each project is determined by both the ProjectTitle and EmpName.
• Each manager has a unique phone number.
Task:
i. Normalize the PROJECT table into the minimal number of relational tables in BCNF.
ii. Use functional dependencies to justify and prove that each relational table obtained from the decomposition of the original PROJECT table is in BCNF.
Task 3 (3.0 marks)
Analysis of relational schemas and normalization
Consider the relational schemas given below and the respective sets of functional dependencies valid in the schemas.
R(A, B, C, D, E, H)
Functional Dependency: A → B, BC → D, E → C, D → A
(i) Find all the minimal super key of the relational table R . List the derivations of all minimal keys.
(ii) Identify the highest normal form. of the relational R. Remember that the identification of a normal form. requires analysis of the valid functional dependencies.
(iii) Decompose the relational table R into minimal number of normalized relational tables in BCNF. Remember to indicate the primary key and foreign keys (if any).
Deliverables
A file solution3.pdf with the outcomes of the steps (i), (ii), and (iii) listed above. Note, that "educated guesses" of the solutions score no marks. You must provide the complete justifications of your answers.
Submission of a file with a different name and/or different extension and/or different type scores no marks!
Submissions
This assignment is due on Tuesday, 22 October 2024 by 9:00 pm (21:00 hours) Singapore time.
Submit the files solution1.pdf, solution2.pdf, and solution3.pdf through Moodle in the following way:
1) Access Moodle at http://moodle.uowplatform.edu.au/
2) To login use a Login link located in the right upper corner the Web page or in the middle of the bottom of the Web page
3) When successfully logged in, select a site CSCI235 (SP424) Database Systems
4) Scroll down to a section Submissions of Assignments
5) Click at Submit your Assignment 1 here link.
6) Click at a button Add Submission
7) Move the files created into an area provided in Moodle. You can drag and drop files here to add them. You can also use a link Add…
8) Click at a button Save changes,
9) Click at check box to confirm authorship of a submission,
10) When you are satisfied, remember to click at a button Submit assignment.