MIS 385 Database Design Assignment I

MIS 385

Database Design Assignment I

Submission instructions

What to submit:

- For part A: one PDF containing the database design outline and ER diagram.

- For part B: the Access file (.accdb) with the database.

Questions

Consider a construction company X that specializes in the development of single-family homes. Company X organizes its internal structure in four operations - Structure, Plumbing, Electrical, and Heating and Ventilation. The company charges its clients by billing the hours spent on each assignment. The hourly billing varies per assignment and is independent of the project, employee, or job.

The table below shows a short portion of a basic report that company X staff would like to see every week concerning what projects are being assigned, the overall assignment hours, and the charges for the assignment. Analyze this figure to understand the kinds of data the company needs to keep and start to think of the relationships that may exist within the data.

Part A.

The goal is to design a relational database that allows Company X to track the data related to their projects. The details of the database are explained next.

Tables:

● Clients: clients are individual people, so the table needs to have columns to keep track of personal data such as name and contact data (address, phone, and email).

● Projects: Projects have a name, a starting date, a planned end date, and an actual end date. We also need information about the location (address of the construction site).

● Employees: This table keeps track of employees’ personal information, such as name, contact (address, phone, and email), and job title.

● Jobs: this table simply keeps track of the kinds of jobs, and is the following:

● Assignments: For each assignment, we need to keep track of the start date and time and number of hours. We also need to know who performed the assignment, which project it is for, and the kind of job it consists of (more on this below).

Do not forget that a primary key is necessary for every table.

Recall: “The company charges its clients by billing the hours spent on each assignment. The hourly billing varies per assignment, and is independent of the project, employee, or job.” Which table should we put the “Charge/hour” in?... Think about it and then add “Charge/hour” to that table.

Relationships:

Note: in this database, we only need one-to-many relationships. No other type of relationship should be used.

● Client – Project: Each project corresponds to one and always one client. Each client may have many projects. A client may also exist in the database and not have any project.

● Assignment – Project: For each assignment, we need to know the project it applies to. Each assignment corresponds to one and always one project. Each project requires many assignments to be completed.

● Assignment – Employee: Each assignment is performed by only one employee. Obviously, each employee has multiple assignments.

● Assignment – Job: For each assignment, we need to know the corresponding job category. Each assignment corresponds to one and always one job. Each job corresponds to 0 or more assignments.

To present the database design, hand in a database design outline and an ER diagram. We have studied what these are in the lectures, but it may be also useful to look at the practice exercises posted in this module to see what these look like.

Part B

You will implement the database you designed in Part A in Ms Access, which means:

● creating the tables;

● choosing reasonable datatypes for each attribute;

● creating relationships between the tables and enforcing referential integrity;

● populating each table with at least 3 records.