CSE 344: Section 5
Database Design
1
Jul 14 , 2022
Design Section
Announcements
2
Jul 14 , 2022
Design Section
Database Design
3
Jul 14 , 2022
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?
4
Jul 14 , 2022
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!
5
Jul 14 , 2022
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;
The only actual_time value is 0
6
Jul 14 , 2022
Design Section
Data Relationship Discovery
How do we find relationships between attributes?
7
Jul 14 , 2022
Domain Approach
E/R Diagrams
Data Approach
Functional Dependencies (FDs)
Boyce-Codd Normal Form (BCNF)
Design Section
ER Diagrams
8
Jul 14 , 2022
Product
Company
Person
employs
buys
makes
price
name
ceo
name
address
address
name
id
Design Section
Relationships
�
9
Jul 14 , 2022
“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
Jul 14 , 2022
Design Section
E/R Demo
11
Jul 14 , 2022
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
Jul 14 , 2022
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
13
Jul 14 , 2022
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