关键词 > COMP2350/6350
COMP2350/6350 Database Systems – Week 4
发布时间:2022-08-25
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
COMP2350/6350 Database Systems
Tutorial (Sample Solution) – Week 4
1. (*)
(a) Describe what the multiplicity constraint represents for a relationship type. (b) What are enterprise constraints and how does multiplicity model these constraints?
(c) How does multiplicity represent both the cardinality and the participation constraints on a relationship type?
Solution:
(a) Multiplicity represents the number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship.
(b) Enterprise constraints are rules that the data in the database must conform to as specified by users or database administrators of a database. Multiplicity constrains the way that entities are related; it is a representation of the policies (or business rules) established by the user or enterprise.
(c) Multiplicity actually consists of two separate constraints:
Cardinality describes the maximum number of possible relationship occurrences for an entity participating in a given relationship type.
Participation determines whether all or only some of the entity occurrences participate in a relationship.
2. (*)
Given the following descriptions, create an appropriate ER diagram for each of the specified relationships.
(a) Each company operates four departments, and each department belongs to one
company.
(b) Each department in part (a) employs one or more employees, and each employee is employed by one department.
(c) Each of the employees in part (b) may or may not have one or more dependants, and each dependant belongs to one employee.
(d) Each employee in part (c) may or may not have an employment history. Solution: (a)-(d)
Company |
Operates |
|
Department |
1..1 |
4..4 |
Department |
Employs |
|
Employee |
1..1 |
1..* |
Employee |
Has |
|
Dependent |
1..1 |
0..* |
Employee |
3. (*)
Represent all of the relationships described in Question 1 as a single ER diagram.
Company |
Employs
Employment History |
Has
Dependent |
4. (*)
The IT Training Group (Glasgow) has contacted you to create a conceptual model by using the Entity-relationship data model for a database that will meet the information needs for its training program. The Company Director gives you the following description of the training group's operating environment.
The Company has twelve instructors and can handle up to one hundred trainees per training session. The Company offers five advanced technology courses, each of which is taught by a teaching team of two or more instructors. Each instructor is assigned to a maximum of two teaching teams or may be assigned to do research.
Each trainee undertakes one advanced technology course per training session. Given this information, draw an ER diagram for IT Training Group (Glasgow).
Solution:
Instructor |
1..1
Teaches
1..*
Training Session |
5. Suppose you are involved in developing a database system for the Sales Department of a company. The operation of the Department can be described as follows.
They have a file of products that they provide to their customers. Each type of product has a unique product number, as well as a description, a cost and a price. The number of the product in stock and the number allocated are updated regularly. When the number in stock decreases to the reorder level, the product is reordered in a pre-decided quantity.
They have a file of customers. Each customer is given a unique customer number. This file also contains customer names that consist of their first and last names, and customer addresses composed of street, city and postcode and the customer telephone number. Each customer has a credit limit, which is used to validate their orders.
A customer may place zero, one or more orders at a time, and an order is always placed by one customer alone. Each order is identified by a unique order number. Other information as to orders includes the date due, the total price, and the status, that is, an order may be outstanding, partially delivered, or fully delivered and invoiced.
An order may involve one or more than one type of products, and a type of products may be involved in more than one order. For each product being ordered in an order, its quantity, total price, and status (i.e., outstanding, partially delivered, or fully delivered) are recorded and updated regularly.
Given this information, draw an ER diagram for this Sales Department, then turn it into a relational database schema.
Solution:
Two possible answers
(a) IsPartOf relationship shown as *:* relationship.
qty status /totalPrice |
0..*
Customer |
custNo {PK} name fName lName address street city postcode telNo creditLimit |
A relational database schema:
Product (productNo , description, price, cost, numberAllocation, reorderQty, reorderLevel, inStock)
Customer ( custNo, fName, lName, street, city, postcode, telNo, creditLimit)
Order (orderNo, status, dateDue, totalPrice, custNo)
OrderDetails ( orderNo, productNo, qty, status, totalPrice)
(b) The IsPartOf *:* relationship decomposed to identify the OrderDetails entity.
A relational database schema for (b):
Product (productNo , description, price, cost, numberAllocation, reorderQty, reorderLevel, inStock)
Customer ( custNo, fName, lName, street, city, postcode, telNo, creditLimit)
Order (orderNo, status, dateDue, totalPrice, custNo)
OrderDetails ( orderNo, productNo, qty, status, totalPrice)
OrderDetails |
qty status /totalPrice |
1..*
PartOf
1..1
Order |
orderNo (PK} status dateDue totalPrice |