CHAPTER 3 - Relational Database Modeling

Database Systems Introduction to Databases and Data WarehousesCHAPTER 3 - Relational Database ModelingCopyright (c) 2016 Nenad Jukic and Prospect Press

INTRODUCTION Relational database model - logical database model thatrepresents a database as a collection of related tables Relational schema - visual depiction of the relational databasemodel Most contemporary commercial DBMS software packages, arerelational DBMS (RDBMS) software packagesJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 2

INTRODUCTIONTerminologyJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 3

INTRODUCTION Relation - table in a relational database A table containing rows and columns The main construct in the relational database model Every relation is a table, not every table is a relationJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 4

INTRODUCTION Relation - table in a relational database In order for a table to be a relation the following conditions must hold:o Each column must have a name (within one table, each column name mustbe unique)o Within one table, each row must be uniqueo Within each row, each value in each column must be single valued (multiplevalues of the content represented by the column are not allowed in any rowsof the table)o All values in each column must be from the same (predefined) domaino Order of columns is irrelevanto Order of rows is irrelevantJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 5

INTRODUCTIONExample of relational and non-relational tablesJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 6

INTRODUCTIONExample of a relation with rows and columns appearing in adifferent orderJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 7

INTRODUCTION Relational database - collection of related relations withinwhich each relation has a unique nameJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 8

PRIMARY KEY Primary key - column (or a set of columns) whose value isunique for each row Each relation must have a primary key The name of the primary key column is underlined in order to distinguishit from the other columns in the relationJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 9

PRIMARY KEYRelation with the primary key underlinedJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 10

MAPPING ER DIAGRAMS INTO RELATIONAL SCHEMAS Once an ER diagram is constructed, it is subsequently mappedinto a relational schema (collection of relations)Jukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 11

MAPPING ENTITIES Mapping entities into relations Each regular entity becomes a relation Each regular attribute of a regular entity becomes a column of the newlycreated relation If an entity has a single unique attribute, then that attribute becomesthe primary key in the resulting mapped relationJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 12

MAPPING ENTITIESEntity mappedinto a relationSample datarecords for themapped relationJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 13

MAPPING ENTITIES WITH COMPOSITE ATTRIBUTES Mapping entities with composite attributes into relations Each component of a composite attribute is mapped as a column of arelation The composite attribute itself does not appear in the mapped relationJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 14

MAPPING ENTITIES WITH COMPOSITE ATTRIBUTESEntity with acomposite attributemapped into arelationSample datarecords for themapped relationJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 15

MAPPING ENTITIES WITH COMPOSITE ATTRIBUTESThe mapped relationas presented to a userin a front-endapplicationJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 16

COMPOSITE PRIMARY KEY Composite primary key - a primary key that is composed ofmultiple columns Column names of a composite primary key are underlined, becausecombined together they form the primary keyJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 17

MAPPING ENTITIES WITH UNIQUE COMPOSITEATTRIBUTES Mapping entities with unique composite attributes intorelations An entity whose only unique attribute is a composite attribute is mappedas a relation with a composite primary keyJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 18

MAPPING ENTITIES WITH UNIQUE COMPOSITEATTRIBUTESEntity with aunique compositeattribute mappedinto a relationSample datarecords for themapped relationJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 19

MAPPING ENTITIES WITH OPTIONAL ATTRIBUTES Mapping entities with optional attributes into relations Optional attribute of an entity is mapped as an optional columnJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 20

MAPPING ENTITIES WITH OPTIONAL ATTRIBUTESEntity with anoptional attributemapped into arelationSample datarecords for themapped relationJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 21

ENTITY INTEGRITY CONSTRAINT Entity integrity constraint - in a relational table, no primary keycolumn can have null (empty) values A rule stating that no primary key column can be optional Every RBMS enforces this ruleJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 22

ENTITY INTEGRITY CONSTRAINTEntity integrity constraint — compliance and violation exampleJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 23

ENTITY INTEGRITY CONSTRAINTEntity integrity constraint — another compliance and violationexampleJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 24

FOREIGN KEY Foreign key - column in a relation that refers to a primary keycolumn in another (referred) relation A mechanism that is used to depict relationships in the relationaldatabase model For every occurrence of a foreign key, the relational schema contains aline pointing from the foreign key to the corresponding primary keyJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 25

MAPPING RELATIONSHIPS Mapping 1:M relationships The relation mapped from the entity on the M side of the 1:Mrelationship has a foreign key that corresponds to the primary key of therelation mapped from the 1 side of the 1:M relationship.Jukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 26

MAPPING RELATIONSHIPSExample Mapping a1:MrelationshipSample datarecords for themapped ERdiagramJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 27

MAPPING RELATIONSHIPS Mapping M:N relationships In addition to the two relations representing the two entities involved inthe M:N relationship, another relation is created to represent the M:Nrelationship itself This new relation has two foreign keys, corresponding to the primarykeys of the two relations representing the two entities involved in theM:N relationship The two foreign keys form the composite primary key of the new relationJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 28

MAPPING RELATIONSHIPSExample Mapping anM:NrelationshipSample datarecords for themapped ERdiagramJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 29

MAPPING RELATIONSHIPSExample Mapping aM:Nrelationshipwith anattributeSample datarecords for themapped ERdiagramJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 30

MAPPING RELATIONSHIPS Mapping 1:1 relationships 1:1 relationships are mapped in the same way as 1:M relationships One of the resulting relations will have a foreign key pointing to theprimary key of another resulting relation One of the mapped relations is chosen to have a foreign key referring tothe primary key of the other mapped relationooIn cases when there is no particular advantage in choosing which resultingrelation will include a foreign key, the choice can be arbitraryIn other cases one choice can be more efficient than the otherJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 31

MAPPING RELATIONSHIPS INTO RELATIONALDATABASE CONSTRUCTSExample Mapping a1:1relationshipSample datarecords for themapped ERdiagramJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 32

REFERENTIAL INTEGRITY CONSTRAINT Referential integrity constraint - In each row of a relationcontaining a foreign key, the value of the foreign key EITHERmatches one of the values in the primary key column of thereferred relation OR the value of the foreign key is null (empty). A rule that defines values that are valid for use in foreign keys In a relational schema lines pointing from the foreign key to thecorresponding primary key are referred to as referential integrityconstraint linesJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 33

REFERENTIAL INTEGRITY CONSTRAINTReferential integrityconstraint —compliance and violationexamplesJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 34

Example ER diagram : ZAGI Retail Company Sales Department DatabaseJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 35

Example mapped relational schema: ZAGI Retail Company Sales Department DatabaseJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 36

Example: Sample data records for the ZAGI Retail Company Sales Department DatabaseJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 37

MAPPING CANDIDATE KEYS Mapping entities with candidate keys (multiple uniqueattributes) into relations One of the candidate keys is chosen by database designer the as aprimary key during the mapping process Other candidate keys are mapped as non-primary key columnsJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 38

MAPPING ENTITIES WITH CANDIDATE KEYS (MULTIPLEUNIQUE ATTRIBUTES) INTO RELATIONSEntity withcandidate keysmappedinto a relationSample datarecords for themapped relationJukić, Vrbsky, Nestorov – Database SystemsCopyright (c) 2016 Nenad Jukic and Prospect PressChapter 3 – Slide 39

relational DBMS (RDBMS) software packages Jukić, Vrbsky, Nestorov – Database Systems Chapter 3 – Slide 2 . Once database requirements are collected and visualized as an ER diagram, the next step in creating a relational database is t\൯ map \ 挀漀渀瘀攀爀琀尩 the ER diagram into a relational schema.\