Data modeling
In this tutorial you’ll learn:
- How to create a conceptual schema of a database.
- How to draw an entity-relationship (ER) diagram.
- How to translate a conceptual model into a logical model.
Prerequisites:
- Having attended Lecture 1.
2 Database of a banking system
The following figure shows the ER diagram with the conceptual schema of a banking system database.
![The conceptual schema of the bank database](/courses/databases/tutorial-1/banking-system-er.png)
Figure 2.1: The conceptual schema of the bank database
Each bank is identified by a unique code and name, and has one or several branches. A branch is responsible for opening accounts and granting loans to customers. Each account is identified by a number (acct_nbr) and is either a checking or savings account (property acct_type). Each customer is identified by its social security number (ssn); a customer can be granted several loans and open as many accounts as s/he wishes.
2.1 Exercises
Exercise
Solution
Since no two banks have the same code_bank or name, either property can be chosen as the primary key of the entity Bank. Both can be considered as valid candidate keys.
Exercise
Solution
The answer is no. While there aren’t any banks that have the same value for {code_bank, name}, two subsets ({code_bank} and {name}) are candidate keys.
Exercise
Solution
Exercise
Solution
The collection of tables is the following:
Bank (code_bank, name, address) Branch (branch_id, address, code_bank) Account (acct_nbr, acct_type, balance, branch_id, ssn) Loan (loan_nbr, loan_type, amount, branch_id, ssn) Customer (ssn, first_name, last_name, telephone, address)
The foreign keys are the following:
Branch(code_bank) → Bank(code_bank).
Account(branch_id) → Branch(branch_id).
Account(ssn) → Customer(ssn).
Loan(branch_id) → Branch(branch_id).
Loan(ssn) → Customer(ssn).
3 Car dealership database
We want to design the database of a car dealership. The dealership sells both new and used cars, and it operates a service facility. The database should keep data about the cars (serial number, make, model, colour, whether it is new or used), the salespeople (first and family name) and the customers (first and family name, phone number, address). Also, the following business rules hold:
- A salesperson may sell many cars, but each car is sold by only one salesperson.
- A customer may buy many cars, but each car is bought by only one customer.
- A salesperson writes a single invoice for each car s/he sells. The invoice is identified by a number and indicates the sale date and the price.
- A customer gets an invoice for each car s/he buys.
When a customer takes one or more cars in for repair, one service ticket is written for each car. The ticket is identified by a number and indicates the date on which the car is received from the customer, as well as the date on which the car should be returned to the customer. A car brought in for service can be worked on by many mechanics, and each mechanic may work on many cars.
3.1 Exercises
Exercise
Solution
Exercise
Solution
The collection of tables is the following:
Car (serial_number, make, model, colour, is_new) Customer (cust_id, cust_first_name, cust_last_name, cust_phone) Invoice (invoice_number, date, price, car_serial_number, sp_id, cust_id) Salesperson (sp_id, sp_first_name, sp_last_name) Mechanic (mec_id, mec_first_name, mec_last_name) Ticket (ticket_number, date_open, date_return, car_serial_number) Repair (ticket_number, mec_id)
The foreign keys are the following:
Invoice(cust_id) → Customer(cust_id).
Invoice(car_serial_number) → Car(serial_number).
Invoice(sp_id) → Salesperson(sp_id).
Ticket(car_serial_number) → Car(serial_number).
Repair(ticket_number) → Ticket(ticket_number).
Repair(mec_id) → Mechanic(mec_id).