Section 4
1
October 22, 2020
Design Section
Database Design
2
October 22, 2020
Database Design
Database Design or Logical Design or Relational Schema Design is the process of organizing data into a database model.
Consider what data needs to be stored and the interrelationship of the data.
Arbitrary Data
Database
There are no good or bad designs, just tradeoffs.
Design Section
Data Relationship Discovery
How do we find relationships between attributes?
Flights dataset:
Did you know that every canceled flight�has an actual_time of 0?
3
October 22, 2020
Design Section
Data Relationship Discovery
Domain approach:
Before looking at the data, �think about the domain
Research the domain and realize that �canceled flights have a 0 flight time!
4
October 22, 2020
Design Section
Data Relationship Discovery
Data mining approach:
Don’t worry about the domain, �just find patterns in the data
Look at the data and notice that every time canceled = 1, then actual_time = 0.
SELECT DISTINCT actual_time
FROM Flights
WHERE canceled = 1;
🡺 only actual_time is 0
5
October 22, 2020
Design Section
Data Relationship Discovery
Relationship types:
6
October 22, 2020
In this class we study functional dependencies
Covered in lecture tomorrow!
Design Section
Data Relationship Discovery
How do we find relationships between attributes?
7
October 22, 2020
Domain Approach
E/R Diagrams
Data Approach
Functional Dependencies
Boyce-Codd Normal Form
(BCNF)
Covered in lecture tomorrow!
Design Section
ER Diagrams
8
October 22, 2020
Product
Company
Person
employs
buys
makes
price
name
ceo
name
address
address
name
id
Design Section
Relationships
�
9
October 22, 2020
“at most one”
“exactly one”
is a
“subclass”
no constraint
other constraint
Design Section
E/R Demo
Create an ER diagram containing the following:
10
October 22, 2020
Design Section
E/R Demo
11
October 22, 2020
The id, name, gender, country of birth, and country region of birth for students and faculty
The major of students
The salary of faculty
The courses that each student takes
The department each course is offered in
Design Section
E/R Demo – Sample Solution
12
October 22, 2020
Design Section
E/R Demo – Sample Solution
13
October 22, 2020
Country (name, region)
People (id, country_name, gender, p_name)
Faculty (People.id, salary)
Student (People.id, major)
Course (courseID, dept)
Took (Student.People.id, Course.courseID)
�
Design Section