CSCI235 1 2024 S4Python

Java Python 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 proj CSCI235 Assignment 1 2024 S4Python ect 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