![Page 1: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/1.jpg)
Database Management
SystemsaProf. Reginald Neil Recario
Institute of Computer Science
University of the Philippines Los Baños
![Page 2: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/2.jpg)
OutlineWe are going to talk about…
![Page 3: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/3.jpg)
Outline
•Data
•Database
•Database Systems
![Page 4: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/4.jpg)
Outline
• Functions and components of a Database Management System
![Page 5: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/5.jpg)
Outline
•Database Normalization
![Page 6: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/6.jpg)
Outline
•Entity Relationship Diagram and Relational Modeling
![Page 7: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/7.jpg)
What is a Data?
![Page 8: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/8.jpg)
What is a Data?
• Facts
• Details of a person (name, home address, etc.)
![Page 9: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/9.jpg)
What is a Database?
![Page 10: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/10.jpg)
What is a Database?
• A collection of logically related data
• Typically visualized as tables
–Column headers
–Rows
![Page 11: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/11.jpg)
What is a Database?Name Degree Program Age
Maria Makiling BS ComputerScience
15
Pedro Penduko BS Statistics 16
Juan Makata BA CommunicationArts
15
![Page 12: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/12.jpg)
What is a Database System?
![Page 13: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/13.jpg)
What is a Database System?
• A database system is simply a database (or databases) with the database management system
![Page 14: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/14.jpg)
What are the functions and components of a Database Management System?
![Page 15: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/15.jpg)
Functions
1.Manipulate data create, retrieve, update and delete
![Page 16: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/16.jpg)
Functions
2.Defining the database metadata
data type, size, and constraints
![Page 17: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/17.jpg)
Functions
3.Process dataperform operations
manipulate data based on an event
![Page 18: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/18.jpg)
Functions
4.Share dataprivileges given to a user
security
![Page 19: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/19.jpg)
Components
A typical DBMS has the following components:
![Page 20: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/20.jpg)
ComponentsInterface
Parser
Query optimizer
Execution engine
Storage
![Page 21: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/21.jpg)
ComponentsInterface
Parser
Query optimizer
Execution engine
Storage
responsible for interacting with the user
![Page 22: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/22.jpg)
ComponentsInterface
Parser
Query optimizer
Execution engine
Storage
checks correctness of syntaxchecks tables and other objects in the query
![Page 23: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/23.jpg)
ComponentsInterface
Parser
Query optimizer
Execution engine
Storage
Simplifies the query for efficient execution
![Page 24: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/24.jpg)
ComponentsInterface
Parser
Query optimizer
Execution engine
Storage
Executes the optimized query
![Page 25: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/25.jpg)
ComponentsInterface
Parser
Query optimizer
Execution engine
Storage
Location where dataand metadata are stored
![Page 26: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/26.jpg)
What is Database Normalization?
![Page 27: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/27.jpg)
What is Database Normalization?• Or simply normalization.
• Reduce or remove redundancy
• Ensure integrity
![Page 28: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/28.jpg)
What is Database Normalization?• Normal forms (NF)
• 1NF, 2NF, 3NF, BCNF, etc.
![Page 29: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/29.jpg)
What is Database Normalization?• 1st Normal form (1NF)
–All attributes within a table/relation must be atomic and single-valued
–Each multivalued attribute must be separated together with the key
![Page 30: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/30.jpg)
What is Database Normalization?STUDENT(Studno, Name, Contact no,
Email address)
Assume a student can have more than one contact number and email address.
![Page 31: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/31.jpg)
What is Database Normalization?Decompose table to satisfy 1NF:
STUDENT(Studno, Name)
STUDENT_CONTACT(Studno, Contact no)
STUDENT_EMAILADDR(Studno, Email address)
![Page 32: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/32.jpg)
What is Database Normalization?Depending on context, attribute Name can still
be broken down to “smaller” attributes:
STUDENT(Studno, Last Name, First Name, Middle Name)
![Page 33: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/33.jpg)
What is Database Normalization?• 2nd Normal form (2NF)
–Attributes of a table/relation must all depend on all keys of the relation
–Functional dependency (FD)
• X → Y
–Full functional dependency
![Page 34: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/34.jpg)
What is Database Normalization?STUDENT(Batch year, Number, Name,
Expected year of graduation)
Batch year is the year of entry (e.g. 2017)
We assume expected year of graduation to be four years.
Batch year and the Number combined is the student number (e.g. 2017-12345)
![Page 35: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/35.jpg)
What is Database Normalization?STUDENT(Batch year, Number, Name,
Expected year of graduation)
FDs:
{Batch year, Number} → {Name}
{Batch year} → {Expected year of graduation}
![Page 36: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/36.jpg)
What is Database Normalization?STUDENT(Batch year, Number, Name)
STUDENT_EXPGRAD(Batch year, Expected year of graduation)
![Page 37: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/37.jpg)
What is Database Normalization?• 3rd Normal form (3NF)
–Attributes within the relation must depend only on a key
–No transitive functional dependency
![Page 38: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/38.jpg)
What is Database Normalization?STUDENT(Studno, Name, Units
earned, Standing)
Units Earned is the number of units earned (taken and passed) by the student
Standing refers to whether the student is a freshman, sophomore, junior or senior based on the earned units.
![Page 39: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/39.jpg)
What is Database Normalization?STUDENT(Studno, Name, Units
earned, Standing)
FDs:
{Studno} → {Name, Units earned, Standing}
{Units earned} → {Standing}
![Page 40: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/40.jpg)
What is Database Normalization?STUDENT(Studno, Name, Units
earned)
STUDENT_STANDING(Units earned, Standing)
![Page 41: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/41.jpg)
On Entity Relationship Diagram and Relational Modeling
![Page 42: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/42.jpg)
On Entity Relationship Diagram and Relational Modeling• Models represent a system
–Notations
–Rules
–Behaviors / processes
![Page 43: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/43.jpg)
On Entity Relationship Diagram and Relational ModelingRelational Model
– A relation (or table) is composed of columns and rows
– Columns → Attributes
– Rows → a record / data
![Page 44: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/44.jpg)
On Entity Relationship Diagram and Relational ModelingRelational Model
Name Degree Program Age
Maria Makiling BS Computer Science 15
Pedro Penduko BS Statistics 16
Juan Makata BA Communication Arts 15
![Page 45: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/45.jpg)
On Entity Relationship Diagram and Relational ModelingEntity Relationship Model
–A high-level conceptual model
–Describes the data as entities having attributes and each entity having relationships with other entities
![Page 46: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/46.jpg)
On Entity Relationship Diagram and Relational ModelingEntity Relationship Diagram
– ERD
–Diagrammatic notation
–Chen’s notation and Crow foot notation
![Page 47: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/47.jpg)
On Entity Relationship Diagram and Relational Modeling
![Page 48: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/48.jpg)
On Entity Relationship Diagram and Relational Modeling
![Page 49: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/49.jpg)
On Entity Relationship Diagram and Relational Modeling
![Page 50: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/50.jpg)
On Entity Relationship Diagram and Relational ModelingExample: An employee is assigned to a room in the
company. An employee has a name, birthday,salary grade and an employee number which isunique. A room has a room number, room size,room capacity, and room location. A roomnumber can identify the room. A number ofemployees can be assigned to a room. Anemployee must be assigned to a room but a roomis not required to be assigned with an employee.An employee can be assigned to only one room.
![Page 51: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/51.jpg)
On Entity Relationship Diagram and Relational Modeling
![Page 52: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/52.jpg)
On Entity Relationship Diagram and Relational ModelingExample: An employee is entitled with some form of
benefits. These benefits often extend to dependents.A dependent of an employee is defined as a either aparent with at age of at least 64 years old or a child ofat most 18 years old. A dependent is described byhis/her name, birthday, relationship to the employeeand highest educational attainment. The employee isdescribed by his/her name, employee no, age anddepartment. The employee no is unique. It is possiblefor an employee to have no dependents. A dependentmust always have an employee to depend on. Adependent depends only to one employee.
![Page 53: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/53.jpg)
On Entity Relationship Diagram and Relational Modeling
![Page 54: Database Management Systems...What is Database Normalization? STUDENT(Batch year, Number, Name, Expected year of graduation) Batch year is the year of entry (e.g. 2017) We assume expected](https://reader035.vdocuments.us/reader035/viewer/2022071503/6123a91a7be0801e29582f1c/html5/thumbnails/54.jpg)
End.