database engineering: part one
DESCRIPTION
First lecture about Database Engineering: Theory of databases Relational databases Relationships ERM/ERD NormalizationTRANSCRIPT
![Page 1: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/1.jpg)
Database EngineeringXbox Part One
Christoph Becher aka The Cupwww.cupworks.net
![Page 2: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/2.jpg)
Agenda
• About Mii
• Theory of databases
• Relational databases
• Relationships
• ERM/ERD
• Normalization
![Page 3: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/3.jpg)
About MiiI know… the picture is my Live Avatar… because he is
simply beautiful
![Page 4: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/4.jpg)
About Mii
• 25 years• past Bigpoint, currently
InnoGames• studied at Games Academy• worked many years as
freelancer • lecturer for game development• focused on high level
architecture for frontend and backend
• aka Ezio Auditore da Firenze
Health
Mana3745
Strength: 56Agility: 89Intelligence: 423Willpower: 345Vitality: 212
5467
Developer Level 38
![Page 5: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/5.jpg)
Theory of databasespreparation is everything
![Page 6: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/6.jpg)
Theory of databases
A database is an organized collection of data• 1960s - navigational DBMS• 1970s - relational DBMS• late 1970s - SQL DBMS• 1980s - desktop DBMS and object-oriented DBMS• 2000s - NoSQL & NewSQL DBMS• DBMSs (database management systems): software
that interacts with the user, other applications, and the database itself to capture and analyze data
• different kinds of data models
![Page 7: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/7.jpg)
Theory of databases
Common logical data models for databases include:• Hierarchical database model• Network model• Relational model• Entity–relationship model• Enhanced entity–relationship model• Object model• Document model• Entity–attribute–value model• Star schema
![Page 8: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/8.jpg)
Theory of databases
Physical data models include:• Inverted index• Flat file
Other models include:• Associative model• Multidimensional model• Multivalue model• Semantic model• XML database• Named graph
![Page 9: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/9.jpg)
Relational databasesit's getting serious
![Page 10: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/10.jpg)
Relational databases
• a relational database is a database that has a collection of tables of data items
• all of which are formally described and organized according to the relational model
• tables may have additionally defined relationships with each other
• each table scheme must identify a column or group of columns called primary key
• a relationship can then be established between each row in the table and a row in another table by creating a foreign key
• relational model offers various levels of refinement of table organization and reorganization called database normalization
![Page 11: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/11.jpg)
Relationshipsit’s complicated
![Page 12: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/12.jpg)
Relationships
• relationships between tables have quantities, called cardinalities
• this shows how many entities of an entity type can or have to stand in relation with exactly one entity of the other entity type involved in the relationship type (and the other way around)
• to display cardinality there are different notation forms (we use Chen)
• types of cardinalities• 1:1
• 1:n
• n:m
![Page 13: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/13.jpg)
Relationships
• 1:1• in a 1:1 relationship there is exactly one entity assigned to
exactly one other entity
• the primary key of one of the two tables is used as foreign key of the other table in an additional column
• 1:n• an entity on one side of the relationship (master) is confronted
by none, one or more than one entities on the other side (detail)
• the detail table gets an additional column, that receives the primary key of the master table as foreign key
• n:m• there can be any amount of entities in relationships with each
other.
• an additional table is generated for implementation, that contains the primary keys of both tables as foreign keys
![Page 14: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/14.jpg)
ERM/ERDEmergency Room?
![Page 15: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/15.jpg)
ERM/ERD
• Entity Relationship Model/Diagram• part of software engineering (SE)• abstract way of describing a database• three levels of models
• Conceptual data model• Logical data model• Physical data model
• the first stage of information system design uses these models during the requirements analysis
• different notations (part of UML, we use Chen)• Tools: MySQL Workbench and Open ModelSphere
![Page 16: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/16.jpg)
ERM/ERD
entityrelatio
n
attribute
entity
attribute
attribute
attribute
attribute
attribute
attribute
attribute
n m
Chen notation
![Page 17: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/17.jpg)
ERM/ERD
English grammar structure
ER structure
Common noun Entity type
Proper noun Entity
Transitive verb Relationship type
Intransitive verb Attribute type
Adjective Attribute for entity
Adverb Attribute for relationship
Chen proposed the following "rules of thumb" for mappingnatural language descriptions into ER diagrams
![Page 18: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/18.jpg)
Normalizationbe normal in this crazy world
![Page 19: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/19.jpg)
Normalization
• Normalization• is a process of organizing fields and tables of a
relation database• minimizes redundancy and dependency • usually involves dividing large tables into smaller
tables and defining relationships between them• selective denormalization can be performed for
performance reasons• Edgar F. Codd, the inventor of the relational model
introduced the concept of normalization and the first normal form
• currently there are nine normal forms (first three are interesting for us)
![Page 20: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/20.jpg)
Normalization
first normal formA relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from
that domain.
![Page 21: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/21.jpg)
Normalization
address
street
number
city
code
![Page 22: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/22.jpg)
Normalization
second normal formNo non-prime attribute in the table is functionally dependent on a proper subset of any candidate
key.
![Page 23: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/23.jpg)
Normalization
id name track title
2001 High Voltage 1 Baby Please Don't Go
2001 High Voltage 2 She's Got Balls
2002 T.N.T. 1 It's a Long Way to the Top
id name
2001 High Voltage
2002 T.N.T.
cd_id track
title
2001 1 Baby Please Don't Go
2001 2 She's Got Balls
2002 1 It's a Long Way to the Top
![Page 24: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/24.jpg)
Normalization
third normal formEvery non-prime attribute is non-transitively
dependent on every candidate key in the table. The attributes that do not contribute to the
description of the primary key are removed from the table. In other words, no transitive
dependency is allowed.
![Page 25: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/25.jpg)
Normalization
id name interpret track
title
2001
High Voltage
AC/DC 1 Baby Please Don't Go
2002
Queen Queen 1 Keep Yourself Alive
id name
101 AC/DC
102 Queen
id track
title cd_id
10 1 Baby Please Don't Go
12001
11 1 Keep Yourself Alive 12002id name interpret_id
12001
High Voltage 101
12002
Queen 102
![Page 26: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/26.jpg)
Normalization
Elementary Key normal FormEvery non-trivial functional dependency in the table is either the dependency of an elementary key attribute
or a dependency on a superkey.
Boyce–Codd normal formEvery non-trivial functional dependency in the table
is a dependency on a superkey.
![Page 27: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/27.jpg)
Normalization
Fourth normal formEvery non-trivial multivalued dependency in the
table is a dependency on a superkey.
Fifth normal formEvery non-trivial join dependency in the table is
implied by the superkeys of the table.
![Page 28: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/28.jpg)
Normalization
Domain/key normal formEvery constraint on the table is a logical
consequence of the table's domain constraints and key constraints.
Sixth normal formTable features no non-trivial join dependencies at all
(with reference to generalized join operator).
![Page 29: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/29.jpg)
Wake up!We are at the end
![Page 30: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/30.jpg)
Questions?Suggestions? Rumors?
![Page 31: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/31.jpg)
Thank youfor all the fish!
![Page 32: Database Engineering: Part one](https://reader036.vdocuments.us/reader036/viewer/2022062319/55810273d8b42a9d198b5263/html5/thumbnails/32.jpg)
& Christoph Becher
www.CupWorks.net
@CupWorks /CupWorks