ER diagram & rational schema

Tasks

1. Provide a conceptual database model for the scenario described below in the form of an entity relationship diagram. Indicate all key and cardinal- ity constraints. Express cardinalities using the min...max notation. Explain important design choices and document relevant assumptions.

2. Create a relational schema for the conceptual model from item (1):

• GivetherelationsR(A1,A2,...)withtheirattributesA1,A2,....

• Underline the primary keys and and indicate the foreign key relations.

Design your relational schema (eliminate tables if necessary) such that the cardinality constraints from the entity relationship diagram are enforced as well as possible.

Annotate constraints that a relational database could check (e.g. nullable and unique). Explain which constraints from the conceptual model cannot be expressed in the relational model.

Scenario description

You are asked to create a database for a small robot manufacturing company. The database is intended to store information about the employees, the manufacturing process and customer orders.

Every employee has a first name, last name, a social security number, a salary, an address and a phone number.

The company produces different types of robots; we will simply refer to them as products in the sequel. For each product we store a product number, a product name, a price and the current amount in stock.

The company has multiple production lines. Each production line produces at last one product. A product can be produced by an arbitrary number of production lines (0 if the product is no longer produced).

Every production line has a unique name and consists of at least one production station. Every station belongs to precisely one production line. Every stations has a number that is unique within the corresponding production line. Every station has at least one employee assigned to it and every employee is assigned to at most one station.

A production station can employ an arbitrary number of machines (possibly 0). Every machine is used in at most one production station. Every machine has a type, and we store for each machine type which employees are qualified to operate it. It is possible that no employee is able to operate some machine.

The database is also intended to store information about customers and orders. Every customer has a first name, last name, an address and a phone number. Design your database in such way that employees can also be customers without storing duplicate information.

A customer can place orders consisting of an arbitrary number of products. Every order has a unique order number and we store what products are ordered in what amount. For each product in the order we also store the price (which can be different from the official product price due to discounts).

Relational Databases Database Design

ID dự án: #29886506

Về dự án