Introduction to Database TP 01
Dr Mohamad AOUDE
October 17, 2023
LU
TP 1: Prepare the Entity Relationship Model
TP 1 is designed to help you ”think as a Database Designer.”
You should know clearly what you need to design a database.
The questions you should ask your client are all about the major
interests of their business, the characteristics of these entities, how
to identify them, and the possible relationships among them.
You should prepare carefully, so when you go back to work on the
Entity Relationship Model, you are well-informed.
In real life, there will be multiple rounds of discussions, clarifications,
and sometimes you may even need to help your clients better
understand their business.
Below you have three small cases. For each of them, think about the
scenario, lay down your assumptions, and create an Entity Relationship
Model based on your assumptions. There is no wrong answer as long as
it makes sense. Once you finish, you can move on to read the solutions.
The solutions are among many possible Entity Relationship Models, so
it’s absolutely fine to be different.
1 Dr Mohamad AOUDE
Case 1: PizzaHot - Story
Your client is a local fast food restaurant - PizzaHot. The background
story is as follows:
PizzaHot is a local restaurant serving the community and bringing
people happiness.
In recent years, PizzaHot is adopting a franchising strategy and is
moving towards a chain-restaurant in the State.
A database system for Inner Management is crucial for the
expansion of the business.
2 Dr Mohamad AOUDE
Case 1: PizzaHot - Story
The information needed is:
Stores: including ID, name, location, contact information.
Employees: including ID, SSN, name, gender, DoB, position, salary,
AffiliateStore.
Vendors: including name, address, contactPerson.
Products: including name, Vendor, price, quantity.
Task: Based on your understanding so far, what are the entities,
attributes, identifiers, and relationships in the Entity Relationship Model?
You can lay out the initial outline here, and the owner will meet with you
to answer your questions.
3 Dr Mohamad AOUDE
Instructions
At this initial stage, your task is to communicate with the client.
You should ask for details about the operation of the business and
the interests of the database.
Whenever you have something unclear, you should first turn to the
client for clarification.
When the client is not available, you can make a decision based on
your understanding, and you should always confirm with the client
later on before moving to the next step.
4 Dr Mohamad AOUDE
Case 1 Solution: PizzaHot
Before confirming with the client, possible entities and their attributes,
identifiers are:
Stores:
StoreID (Primary Key)
StoreName
Location
Contact
Employees:
EmployeeID (Primary Key)
SSN
Name
Gender
DoB
Position
Salary
5 AffiliateStore Dr Mohamad AOUDE
Case 1 Solution: PizzaHot
Vendors:
VendorName (Primary Key)
Address
ContactPerson
Products:
Name (Primary Key)
Price
Quantity
Description
6 Dr Mohamad AOUDE
Case 1 Solution: PizzaHot
The relationships among these entities are based on my assumptions - I’ll
discuss with the client later on and make necessary modifications.
A store may have many employees, and one employee must work for
one store.
A store may sell many products, and each product can be sold at
many stores.
A product must belong to one and only one vendor, and a vendor
may provide one or more products.
An employee must be supervised by another employee, and an
employee may supervise many other employees.
7 Dr Mohamad AOUDE
8 Dr Mohamad AOUDE
Case 2: TrueTrade - Story
Your client is a stock-exchange company. The background story is as
follows:
TrueTrade provides excellent financial services to customers all over
the world.
Customers can buy/sell stock via TrueTrade easily and securely.
TrueTrade also provides detailed information for every stock so
customers can make the best decision.
8 Dr Mohamad AOUDE
Case 2: TrueTrade - Story
The information needed for TrueTrade is:
Stocks: including stocknb, companyName, ExchangeMarket,
Description.
Customers: including CustomerID, Name, Contact.
Portfolio: including CustomerName, currentValue.
Logs: including stocknb, openPrice, closePrice, date.
Transactions: CustomerID, Portfolio, stock, quantity, price, date,
time.
Task: Based on your understanding so far, what are the entities,
attributes, identifiers, and relationships in the Entity Relationship Model?
You can lay out the initial outline here, and the owner will meet with you
to answer your questions.
9 Dr Mohamad AOUDE
Case 2 Solution: TrueTrade
Before confirming with the client, possible entities and their attributes,
identifiers are:
Stocks:
stock (Primary Key)
companyName
exchangeMarket
description
Logs:
stock (Primary Key)
date (Primary Key)
openPrice
closePrice
Portfolio:
customerName
10 currentValue Dr Mohamad AOUDE
Case 2 Solution: TrueTrade
Customers:
customerID (Primary Key)
name
phone
email
address
Transactions:
TransactionID (Primary Key)
customerID
Portfolio
stock
quantity
price
date
time
11 Dr Mohamad AOUDE
Case 2 Solution: TrueTrade
The relationships among these entities are based on my assumptions - I’ll
discuss with the client later on and make necessary modifications.
A customer must have one or more portfolios, and a portfolio must
belong to one and only one customer.
A portfolio may have one or more stocks, and a stock may belong to
one or more portfolios.
A stock must have one or more logs, and one log must belong to
one and only one stock.
A customer may have one or more transactions, and each
transaction must be done by one and only one customer.
A portfolio may be in one or more transactions, and each transaction
must include one and only one portfolio.
A transaction must have a stock, and a stock may be in one or more
transactions.
12 Dr Mohamad AOUDE
13 Dr Mohamad AOUDE
Case 3: clinic ERD Guidelines
For creating ERDs, make sure you are using Crow’s foot notation for
ERDs, and including key components: Entities, Attributes (if provided),
Identifiers (if provided), Relationships (with correct cardinality and
participation multiplicities).
For interpreting ERDs, make sure you are including Entities, the explicit
terms for cardinality and participation for the relationships, such as must,
may, can, one and only one, one or more, many, etc.
13 Dr Mohamad AOUDE
Case 3 Question 1: Create a clinic ERD
Read the following statements and draw the Entity Relationship Diagram
using Crow’s Foot Notation.
The ER Model is as below:
A clinic must have one or more doctors; A doctor may belong to one
or more clinics.
A patient may visit one or more doctors; A doctor may take one or
more patients.
A doctor may have one and only one assistant nurse; An assistant
nurse must work for one or more doctors.
A patient must have one and only one history record; A record must
belong to one and only one patient.
Now you can draw the ER Diagram based on the ER Model.
14 Dr Mohamad AOUDE
Case 3 Interpret an ERD
Read and understand the ERD below, and answer the questions:
A: What are the entities in the ERD?
B: Translate this ERD to a list of statements. Make sure you
address the cardinality and participation using correct terms may,
must, one and only one, one or more, etc.
15 Dr Mohamad AOUDE
Case 3 Question 1 Solutions
16 Dr Mohamad AOUDE
Case Cars
17 Dr Mohamad AOUDE
Case 3 Question 2 Solutions
2A: Entities: Cars, Supervisors, Drivers, Division, DetailProfile,
Dependents
2B:
A car may be operated by one or more drivers; a driver may operate
one or more cars.
A driver must be supervised by one and only one supervisor, and a
supervisor must supervise one or more drivers.
A driver must have a detail Profile, and a detailProfile may belong
to one driver.
A driver must belong to one division, and a division may have one or
more drivers.
A driver may have one or more dependents, and a dependent must
belong to one driver.
18 Dr Mohamad AOUDE
Case 3 Question 3 Solutions
19 Dr Mohamad AOUDE
Case 4: MiniLibrary - Story
Your client is a non-profit organization called MiniLibrary. The
background story is as follows:
MiniLibrary is an organization that promotes neighborhood book
exchanges.
It has more than 3,000 bookcases around the city.
20 Dr Mohamad AOUDE
Case 4: MiniLibrary - Story
The information you need to collect and record is:
Books, including ISBN, COPY, Title, Author1, Author2, Year,
Publisher.
BookCases, including Case, Street, County.
Users, including UserID, Name, Address, Contact.
Log: BookInfo, UserInfo, BookCaseInfo, TimeStamp,
Borrow/Return.
Task: Based on your understanding so far, what are the entities,
attributes, identifiers, and relationships in the Entity Relationship Model?
You can lay out the initial outline here, and the owner will meet with you
to answer your questions.
21 Dr Mohamad AOUDE
EXR1 City Library Database ERD
22 Dr Mohamad AOUDE
Entities: Book
Attributes
ISBN (Primary Key)
Title
Pages
Genre
Relationships
Written by one Author (1, 1)
23 Dr Mohamad AOUDE
Entities: Author
Attributes
AuthorID (Primary Key)
AuthorName
Relationships
Writes several Books (1, N)
24 Dr Mohamad AOUDE
Entities: Staff
Attributes
StaffID (Primary Key)
StaffName
Relationships
Issues many Books to a specific Borrower (1, N)
25 Dr Mohamad AOUDE
Entities: Borrower
Attributes
BorrowerID (Primary Key)
BorrowerName
Relationships
Can borrow many Books in a single attempt (1, N)
Owns one Membership Card (1, 1)
26 Dr Mohamad AOUDE
Entities: Membership Card
Attributes
CardID (Primary Key)
Relationships
Given to a specific Borrower (1, 1)
27 Dr Mohamad AOUDE
Author-Book Relationship
Description
An Author writes exactly one Book, and a Book is written by exactly
one Author.
Cardinality: (1, 1) where 1 Author is related to 1 Book.
28 Dr Mohamad AOUDE
Staff-Borrower Relationship
Description
Each Staff member issues many Books to a specific Borrower, and a
Borrower can have many Books issued by different Staff members.
Cardinality: (1, N) where 1 Staff member is related to N Books
issued to N Borrowers.
29 Dr Mohamad AOUDE
Borrower-Membership Card Relationship
Description
A Borrower owns one Membership Card, and a Membership Card is
given to a specific Borrower.
Cardinality: (1, 1) where 1 Borrower is related to 1 Membership
Card.
30 Dr Mohamad AOUDE
31 Dr Mohamad AOUDE
EXR 2 Instructions
Given the E-R diagram below, your task is to write the business rules
and interpret multiplicities that might exist in each relationship in
the diagram. You can use draw.io or any diagram creation tool to
create your own diagram.
You should identify the correct business rules that are represented in
the Entity-Relationship Diagram (ERD).
Once you have identified the correct business rules, answer the
question below, and the solution will unlock.
31 Dr Mohamad AOUDE
Business Rules and Multiplicities
Rule 1: A/one/each book is sold to a customer at a specific branch.
Rule 2: Each branch stores the branchCode, Name, City, and ZipCode.
Rule 3: A/one/each customer can recieve many/several/multiple sales
but a sale can be done by a/specific/exactly one customer.
Rule 4: Sales stores bookID, customerID, price, and dateTime of a
specific sale.
Rule 5: A sale can contain multiple books, but a/one/each book belongs
to one sale.
Rule 6: A sale is made at one branch, but a/one/each branch can have
many/several/multiple sales. .
32 Dr Mohamad AOUDE
33 Dr Mohamad AOUDE
EXR 3 Instructions
Below is the UML ERD that does not fully represent the business rules
below.
Using the business rules, create a new ERD that improves its design
quality so that it can be successfully implemented in a relational
database.
You can use draw.io to create your diagram. Once you have created your
diagram, answer the question below, and the solution will unlock.
33 Dr Mohamad AOUDE
EXR 3 Instructions
Business Rules:
A student can register for more than one degree over time, and
many students can register for the same degree.
Each degree consists of many modules that you have to complete,
and a module can be part of multiple degrees. The number of
credits for the module should be stored in the database.
A student can register multiple times for the same module, the final
mark and grade should be recorded for each module registration.
The name, surname, and student number should be recorded for
each student.
A student also registers to study at a specific campus, and each
campus has a name.
34 Dr Mohamad AOUDE
34 Dr Mohamad AOUDE
35 Dr Mohamad AOUDE
EXR 3 P2 Translate the E-R diagram into Relation Schemas
Consider the ERD from Activity above, write out the relation schemas in
the format shown below:
Example:
Student (StudentID, StudentName, StudentCellPhone, .... etc.)
35 Dr Mohamad AOUDE
Relation Schemas
Campus (CampusID, CampusName)
Student (StudentNo, Name, Surname)
Registration (StudentNo, ModuleCode, CampusID, FinalMark,
Grade)
Module (ModuleCode, ModuleName, Credit)
Degree (DegreeCode, DegreeName)
Degree Module (DegreeCode, ModuleCode)
Award (StudentNo, DegreeCode)
36 Dr Mohamad AOUDE
EXR 4 Instructions
Given the business rules below, practice creating an E-R diagram.
Book Library Business Rules:
1. Each book has a unique ISBN. The ISBN attribute of the Book
entity must have a unique value for each book.
2. A book can have multiple authors, and an author can have written
multiple books. AuthorID, author firstname, lastname, and total
number of books per author must be stored in the database.
37 Dr Mohamad AOUDE
Instructions
3. Each book can be categorized not more than three genres, and a
genre can have multiple books. GenreID, name of genre, and total
number of books per genre should be captured.
4. Each book has a specific due date for return. The Due Date
attribute of the Book entity must store the date when the book is
due for return.
5. A member may borrow multiple books, and each book can be
borrowed by only one member.
6. Each member has a unique library card number. The Library Card
Number attribute of the Member entity must have a unique value
for each member.
38 Dr Mohamad AOUDE
Instructions
7. A member may have multiple fines, and each fine is associated with
a specific member. Member captures firstname and lastname, while
Fine records date and type of the fine.
8. The library can have at least one branch, and each branch can have
multiple books. The library should store the name of the library, city,
and library code as a unique identifier. Branch stores branchID and
name.
9. Each book has title and a specific number of available copies. The
Available Copies attribute of the Book entity must store the count
of copies currently available for borrowing.
39 Dr Mohamad AOUDE
