please use laptop ,i want to know how to work on laptop please . SmartProjects C
ID: 3881634 • Letter: P
Question
please use laptop ,i want to know how to work on laptop please .
SmartProjects 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:
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
User view 1 requirement: Each employee has a unique ID number, and their first name, last name, date of birth, address, contactNo user name and password must be recorded. There is a need to keep track of the dependents of each employee for insurance purposes.
User view 2 requirements: Each customer has a unique customer_number 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, managerID, managerName,managerContactNo 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 timAe.
A. Determine the functional dependences.
E.g., CustomerID à name, address, ......
B. Then normalise these tables. Make the normalization to 3NF. State for every step in the
Explanation / Answer
1
EMPLOYEE:
empid -> fname, lname, dob, address, contact, username, password, PNum, project_title, dept_id
CUSTOMER:
customer_number -> fname, lname, address, PNum, payment
PROJECT:
PNum -> project_title, StartDate, managerID, managerName, managerContactNo, dept_id(multivalued)
DEPARTMENT
dept_id -> dept_name, PNum (multivalued)
2.
First convert it into 1NF (multivalues items removed)
EMPLOYEE:
empid -> fname, lname, dob, address, contact, username, password, PNum, project_title, dept_id
CUSTOMER:
customer_number -> fname, lname, address, PNum, payment
PROJECT:
PNum -> project_title, StartDate, managerID, managerName, managerContactNo
DEPARTMENT
dept_id -> dept_name
ASSOCIATION
dept_id <-> PNum
then convert it into 2NF (revome partial dependency)
EMPLOYEE:
empid -> fname, lname, dob, address, contact, username, password, PNum, project_title, dept_id
CUSTOMER:
customer_number -> fname, lname, address, PNum, payment
PROJECT:
PNum -> project_title, StartDate, managerID, managerName, managerContactNo
DEPARTMENT
dept_id -> dept_name
ASSOCIATION
dept_id <-> PNum
Finnaly we convert it to 3NF (remove transitive dependencies)
EMPLOYEE:
empid -> fname, lname, dob, address, contact, username, password, PNum, dept_id
CUSTOMER:
customer_number -> fname, lname, address, PNum, payment
PROJECT:
PNum -> project_title, StartDate, managerID
(ManagerID is foreign key reffereing empid)
DEPARTMENT
dept_id -> dept_name
ASSOCIATION
dept_id -> PNum
Dis this help you? If not please let me know via the comment section. I shall try my level best to remove all your doubts
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.