Relational Database Design CH 2 Week 3 The

Relational Database Design CH 2 Week 3

The workflow – DBMS independent Customers domain Busin ess lo g Use C ase d ia ic gram ? Requirements collecting and analysis Functional requirements Data requirements Functional analysis Conceptual design High level transaction specification t ed n i f e R D Conceptual schema Logical design Data model mapping sigh r e v o del o m ata

The workflow – DBMS dependent Logical design Data model mapping el es Tabl Application program design ped p a m Logical Schema (mapping to fields and tables) Physical design Transaction implementation Application Programs in od m e h t Internal Schema (in the DBMS) Code, T-SQL, pizza and beer es in l b a T th /D e DB DL

Data modelling – ER diagram Entity types Part of Engages in Relationships Part of Based on Attributes Keys Is a

The Entity Box Type Defines a set of attributes with the same domain An entity is a unity of fields Should be able to stand alone as a set of data That gives meaning Name Surname Temperature Jesper Hansen 23 Gurli Magrethe 32

The entity – ready for relationships Entity must pass the first 3 normalization rules (for a later lesson!) No 2 rows must be exactly the same data combined – all rows must be distinct Better expressed as no redundancy

The entity Strong entity Weak entity Key Name Surname Foreign Key 1 Jesper Hansen 44 2 Gurli Magrethe 45 Dependency on own key to create distinc rows Dependency on foreign key to create distinc rows

Entity sets Entity set Column Data Data Data data We want to relate entitysets together!

Attributes Atomic Composite Atomic = which can´t be divided further Example studen´s phone number is atomic Value of 10 digits. Entity

Attributes Atomic Composite Atomic Value Multivalue Value Entity Key candidate Derived

Keys A unique fieldset that can 1 or more fields Candidate key On or more fields considered as key(s) Primary key (key) The key of the current table Foreign key The key(s) imposed upon the table Compound key A key consisting solely of foreign key(s) MUST Guarantie a distinc row at any time Composite key A key consisting of more than one field Natural key Composite key of natural accuring fields Surrogate key A key internally created as a new field

Relationalships Cardinality of sets 0. . * X. . Y X Key 1 Foreign Key One to many Key Foreign Key * * Many to one 1 Foreign Key Many to many *

Relationalships One-to-One relationship customer table customer details table cust_id lastname firstname cust_id height weight dateofbirth 0001 henry john 0001 181 75 03/05/1960 0002 smith adam 0002 179 82 06/08/1974 0003 johnson mary 0003 171 65 04/01/1955 0004 bailey harry 0004 185 93 05/05/1980 0005 morgan alex 0005 168 71 09/09/1975

Relationalships One-to-One relationship However, if you think about it carefully, the above relationship does not really bring any design benefits. In fact, it would cause performance overheads to the database engine for having to link the table rows together to service user queries related to customers. The 2 tables can actually be combined into a single table as illustrated cust_id lastname firstname height weight dateofbirth 0001 henry lohn 181 75 03/05/1960 0002 smith adam 179 82 06/08/1974 0003 johnson mary 171 65 04/01/1955 0004 bailey harry 185 93 05/05/1980 0005 morgan alex 168 71 09/09/1975

Relationalships Many to many relationship authors table books table author_id lastname firstname 0001 0002 0003 0004 henry smith johnson bailey john adam mary harry book_id 0001 0002 0003 0004 0005 title A database primer Building a datawarehouse Teach yourself SQL 101 exotic recipes Visiting europe author_id 0001 0002 0004

Relationalships Key Data 2 Foreign key Key Data 1 1 Vbvcbcvb 2 1 Dfsdfsdfs 2 Bvcbcvbcv 3 2 Fsdfsdfsdfsd 3 fgdfgfdgdf NULL Pulled dataset via SQL Data 1 Data 2 (Using the join commands)

Relationalships Recursive relationship Military person 1 Commands *

Relationalships Binary relationship Person Owns House

Relationalships Tertiary relationships Person Owns Morgage House

Relationalships N-ertiary relationships Person Owns Morgage House