Post date: Jan 19, 2019 5:58:3 AM
Chapter 7: Data Modeling Using Entity-Relationship (ER) Model
High-level conceptual data models
Entity types, entity sets, attributes, and keys
Relationship types, relationship sets, roles, and structural constraints
Weak entity type
ER diagrams, naming conventions, and design issues
Interesting websites:
In-Class Exercises:
Ex 1:
Problem :
A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company.
Draw an ER diagram that captures this information.
Ex 2:
Translate the following in a ER diagram:
Customers (Cust-No) get discounts (Disc-No) on items (Item-No). Each item can only have one discount rate.
Items belong to a single category (Categ-ID).
Ex 3:
Translate the following in a ER diagram:
A supplier (Supp-#) processes many purchase orders (PO-#) and sells a number of products (Prod-#). A product is sold by only one supplier.
Each of the purchase orders that a supplier may process includes several products. The relationship between order and product is defined as "Line Item".
A customer (Cust-#) may place one or more purchase orders. A purchase order may be placed by only one customer.
Ex 4:
Transfer the following description into an ER Diagram:
A company has one or more locations referred to as "Plants" (Plant-ID). Several plants are uniquely assigned to the same company code (CC-ID).
Plants can have several storage locations (S-loc-#) in which the manufactured and purchased items are stocked, a storage location however, can only be assigned to one plant.
A purchase organisation (PO-ID) can be assigned to several plants and a plant to several purchase organisations. A company code is related to one or more purchase organisations, the latter is uniquely related to a company code.
A distribution channel (DC-#) can be assigned to several divisions (Div-ID), whereas a division is uniquely assigned to a distribution channel.
A sales organisation (Sales-Org-ID) can have several distribution channels, whereas a distribution channel is uniquely assigned to a sales organisation.
A company code can have several sales organizations. Sales organisations are assigned to one company code only and to several plants. Plants can also be assigned to several sales organisations.