Please work on the laptop please , i want to know how to work..

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 A and B.

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 time .

A. Represent the structure of your database visually by using the entity-relationship (E-R) diagram. If you make any assumptions about data that doesnt show from the problem, they must be described.

You can use Visio or any other software tool to create the ER diagram.

B. Build this model using MS Access by creating these tables and relationships. Populate these tables with appropriate data, at least 3 records in each table.

And i creat table blow , it might helps .

a. Analyse all the user requirements given above; identify and list all entities described in each user requirement.

All the entities would be:

1. Employee

2. Customer

3. Project

4. Manager

5. Department

6. Department_project

7. Depandants

b. Add attributes to these entities and represent these entities (or tables) and attributes as a collection of tables and attributes. You are required to arrange them as given in the example below.

NB: Select a suitable primary key for each table and underline them.

E.g., Customer (CustomerID, name,.

Entities and attributes -

1. Employee ( int ID_number, first_name, last_name, date date_birth, address, double phoneno, string user_name, string password, int PNum, int dept_num) ;

2. Depandents ( int ID_number, name,double phoneno );

3. Customer ( int cust_number, string first_name, string last_name, string address, int PNum, int payment) ;

4. Project ( int PNum, string project_title, date start_date, int manager_id) ;

5. Manager (int manager_id, string manager_name, double phoneno);

6. Department ( int dept_num, string dept_name );

7. department_project (int dept_num, int PNum);

c. Outline all relationships between entities.

E.g., One project may belong to several departments - One-to-many,

Outline all relationships between entities.

many depandants may belong to one employee - many to one

many employee may work on one project - many to one

one department can have many employees - one to many

one employee can only belong to one department - one to one

A department controls a number of projects and One project may belong to several departments. - many to many

One customer can submit only one project at one time - one to one

d. Determine the functional dependences.

E.g., CustomerID name, address, ......

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)

e. Then normalise these tables. Make the normalization to 3NF. State for every step in the

First convert it into 1NF

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

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 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