The table i made below is:Employee (emp_id, first_name, last_name, dob, address,

Question

The table i made below is:Employee (emp_id, first_name, last_name, dob, address, contactNo, username, password)

Dependent (dependent_id, name, relation, age, emp_id)

Customer (customer_id, first_name, last_name, address)

Project (PNum, title, startDate, managerID, managerName, managerContactNo, customer_id)

Project_Assignment (PNum, emp_id)

Department (dept_id, name)

Emp_dept (dept_id, emp_id)

Dept_proj (dept_id, PNum)

the identified functional dependencies in the identified entities-

emp_id -> name, dob, address, dept_id, PNum

customer_id -> name, address, PNum

PNum -> dept_id, customer_id, emp_id, managerID

The question is

SmactPreiests Company is planning to design a data model to hold information relating to their projects, employees working on company projects, and their customers. SmartProjects IT Manager provided the following business rules to the software developer: 1. Complete the information-level design for the database that satisfies the above constraints and user view requirements. In order to complete this information-level design you are required to answer the questions given below 1(a)-(e) User view 1 requirement: Each employee has a unique ID number, and their first name, last name, date of birth, address, SSOtact user name and password must be recorded. There is a need to keeptrack of the dependents of each employee for insurance purposes. User view 2 requirements: Each customer has a unique customer.oumber and their first name, last name, address, PNum, project tile and payment must be recorded. User view 3 requirements: Each project has a project number that uniquely identifies the project (PNum) and project title, StartDate,manaemanagerNaemaagerContactNo must be recorded. User view 4 requirements: An employee can work on only one project. A project can have one or more employees associated with it. User view 5 requirements: Each department has a unique ID number and name. A department can have many employees. An employee can only work in one department. A department controls a number of projects. User view 6 requirements: One project may belong to several departments. One customer can submit only one project at one time.

Explanation / Answer

Third normal form:

A relation is in third normal form if it is in 2NF and no non key attribute is transitively dependent on the primary key.

A bank uses the following relation:

Employee

emp_id

first_name

last_name

dob

address

contactNo

username

password

Dept_id

Dependent

dependent_id

name

relation

age

emp_id

The following dependencies exist:

1. first_name, last_name, dob,address,contactno,username,password are functionally dependent on emp_id(emp_id --> first_name, last_name, dob,address,contactno,username,password)

2. Dependent is functionally dependent on dependent_id (dependent_id --> Dependent )

The table in this example is in 1NF and in 2NF. But there is a transitive dependency between dependent_id and Dependent, because dependent_id is not the primary key of this relation. To get to the third normal form (3NF), we have to put the Dependent name in a separate table together with the clearing number to identify it.

Customer

customer_id

first_name

last_name

address

Project

PNum

title

startDate

managerID

managerName

managerContactNo

customer_id

Department    

dept_id

name

Dept_proj

Assignment_id

PNum

dept_id

Emp_id

Here you can see that one project can be allocated to multiple dept,so Here I am using dept_id and foreign key.

emp_id

first_name

last_name

dob

address

contactNo

username

password

Dept_id

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.