4 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 relation

5 INTRODUCTION Relation - table in a relational database In order for a table to be a relation the following conditions must hold: o o o o o o Each column must have a name (within one table, each column name must be unique) Within one table, each row must be unique Within each row, each value in each column must be single valued (multiple values of the content represented by the column are not allowed in any rows of the table) All values in each column must be from the same (predefined) domain Order of columns is irrelevant Order of rows is irrelevant

6 INTRODUCTION Example of relational and non-relational tables

7 INTRODUCTION Example of a relation with rows and columns appearing in a different order

8 INTRODUCTION Relational database - collection of related relations within which each relation has a unique name

9 PRIMARY KEY Primary key - column (or a set of columns) whose value is unique for each row Each relation must have a primary key The name of the primary key column is underlined in order to distinguish it from the other columns in the relation

10 PRIMARY KEY Relation with the primary key underlined

11 MAPPING ER DIAGRAMS INTO RELATIONAL SCHEMAS Once an ER diagram is constructed, it is subsequently mapped into a relational schema (collection of relations)

12 MAPPING ENTITIES Mapping entities into relations Each regular entity becomes a relation Each regular attribute of a regular entity becomes a column of the newly created relation If an entity has a single unique attribute, then that attribute becomes the primary key in the resulting mapped relation

14 MAPPING ENTITIES WITH COMPOSITE ATTRIBUTES Mapping entities with composite attributes into relations Each component of a composite attribute is mapped as a column of a relation The composite attribute itself does not appear in the mapped relation

15 MAPPING ENTITIES WITH COMPOSITE ATTRIBUTES Entity with a composite attribute mapped into a relation Sample data records for the mapped relation

16 MAPPING ENTITIES WITH COMPOSITE ATTRIBUTES The mapped relation as presented to a user in a front-end application

17 COMPOSITE PRIMARY KEY Composite primary key - a primary key that is composed of multiple columns Column names of a composite primary key are underlined, because combined together they form the primary key

18 MAPPING ENTITIES WITH UNIQUE COMPOSITE ATTRIBUTES Mapping entities with unique composite attributes into relations An entity whose only unique attribute is a composite attribute is mapped as a relation with a composite primary key

20 MAPPING ENTITIES WITH OPTIONAL ATTRIBUTES Mapping entities with optional attributes into relations Optional attribute of an entity is mapped as an optional column

21 MAPPING ENTITIES WITH OPTIONAL ATTRIBUTES Entity with an optional attribute mapped into a relation Sample data records for the mapped relation

22 ENTITY INTEGRITY CONSTRAINT Entity integrity constraint - in a relational table, no primary key column can have null (empty) values A rule stating that no primary key column can be optional Every RBMS enforces this rule

25 FOREIGN KEY Foreign key - column in a relation that refers to a primary key column in another (referred) relation A mechanism that is used to depict relationships in the relational database model For every occurrence of a foreign key, the relational schema contains a line pointing from the foreign key to the corresponding primary key

26 MAPPING RELATIONSHIPS Mapping 1:M relationships The relation mapped from the entity on the M side of the 1:M relationship has a foreign key that corresponds to the primary key of the relation mapped from the 1 side of the 1:M relationship.

32 MAPPING RELATIONSHIPS Mapping M:N relationships In addition to the two relations representing the two entities involved in the M:N relationship, another relation is created to represent the M:N relationship itself This new relation has two foreign keys, corresponding to the primary keys of the two relations representing the two entities involved in the M:N relationship The two foreign keys form the composite primary key of the new relation

36 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 the primary key of another resulting relation One of the mapped relations is chosen to have a foreign key referring to the primary key of the other mapped relation o o In cases when there is no particular advantage in choosing which resulting relation will include a foreign key, the choice can be arbitrary In other cases one choice can be more efficient than the other

38 REFERENTIAL INTEGRITY CONSTRAINT Referential integrity constraint - In each row of a relation containing a foreign key, the value of the foreign key EITHER matches one of the values in the primary key column of the referred 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 the corresponding primary key are referred to as referential integrity constraint lines

43 MAPPING CANDIDATE KEYS Mapping entities with candidate keys (multiple unique attributes) into relations One of the candidate keys is chosen by database designer the as a primary key during the mapping process Other candidate keys are mapped as non-primary key columns

46 MAPPING MULTIVALUED ATTRIBUTES Mapping entities with multivalued attributes into relational database constructs An entity containing the multivalued attribute is mapped without the multi-valued attribute The multi-valued attribute is mapped as a separate relation that has a column representing the multivalued attribute and a foreign key column referring to the primary key of the relation resulting from the entity itself o Both of these columns form a composite primary key for the separate relation

55 MAPPING UNARY RELATIONSHIPS Mapping M:N unary relationships In addition to the relation representing the entity involved in a unary M:N relationship, another relation is created to represent the M:N relationship itself This new relation has two foreign keys, both of them corresponding to the primary key of the relation representing the entity involved in the unary M:N relationship Each of the foreign keys is used as a part of the composite primary key of the new relation

59 MAPPING MULTIPLE RELATIONSHIPS BETWEEN THE SAME ENTITIES Mapping multiple relationships between the same entities Each relationship is mapped

60 MAPPING MULTIPLE RELATIONSHIPS BETWEEN THE SAME ENTITIES Mapping multiple relationships between the same entities Sample data records for the mapped relations

61 MAPPING WEAK ENTITIES Mapping weak entities Weak entities are mapped in a same way as regular entities with one addition: o The resulting relation has a composite primary key that is composed of the partial identifier and the foreign key corresponding to the primary key of the owner entity

69 RELATIONAL DATABASE CONSTRAINTS Relational database constraints - rules that a relational database has to satisfy in order to be valid Implicit constraints o The implicit relational database model rules that a relational database must satisfy in order to be valid User-defined constraints o Database constraints that are added by the database designer

70 RELATIONAL DATABASE CONSTRAINTS Implicit constraints Each relation in a relational schema must have a different name Each relation must satisfy the following conditions: o o o o o o Each column must have a different name Each row must be unique In each row, each value in each column must be single valued Domain constraint - all values in each column must be from the same predefined domain The order of columns is irrelevant The order of rows is irrelevant Primary key constraint - each relation must have a primary key, which is a column (or a set of columns) whose value is unique for each row Entity integrity constraint Referential integrity constraint

78 Example: An M:N relationship and associative entity mapped into a relation in the same way

79 MAPPING TERNARY RELATIONSHIPS Mapping ternary relationships Ternary relationships are used as many-to-many-to-many relationships A new relation is created with foreign keys from the participating entities forming a composite primary key of the new relation

80 Example: Mapping a ternary relationship

81 Example: Sample data records for the mapped relations

82 DESIGNER-CREATED PRIMARY KEYS AND THE AUTONUMBER OPTION Autonumber data type option - enables automatic generation of consecutive numeric data values in a column Designer-created primary key - primary key column, not called for by the original requirements, added to a table by the database designer Often used in conjunction with the autonumber data type option

83 DESIGNER-CREATED PRIMARY KEYS AND THE AUTONUMBER OPTION Entity and the resulting relation with a Entity and the resulting relation designer-created primary key column Sample data records for the relation with a designer-created primary key

84 ER AND RELATIONAL MODELING Process of requirements collection should be accompanied by the ER modeling and then followed by mapping the ER model into a subsequent relational schema Some practitioners prefer to create relational schemas straight from the requirements In such cases, the ER modeling phase is simply omitted

85 ER AND RELATIONAL MODELING Create relational schemas straight from the requirements is not advisable for following reasons ER modeling is more suited for visualization of the requirements Certain concepts can be visualized graphically only in ER diagrams Every attribute is mentioned only once in the ER diagram An ER model is a better communication and documentation device

DATABASE DESIGN - The ability to design databases and associated applications is critical to the success of the modern enterprise. - Database design requires understanding both the operational and business

COMP 378 Database Systems Notes for Chapter 7 of Database System Concepts Database Design and the Entity-Relationship Model The entity-relationship (E-R) model is a a data model in which information stored

Connectivity and Cardinality The term connectivity is used to describe the relationship classification. Cardinality expresses the minimum and maximum number of entity occurrences associated with one occurrence

ETEC 2601 Database Systems Chapter 5: Data Modeling With The Entity-Relationship Model Copyright 2004-2010 J. B. Gallaher The Data Model The design of a database Is the way the user conceives the information

Constraints in ER Models CS 317, Fall 2007 Types of Constraints Keys are attributes or sets of attributes that uniquely identify an entity within its entity set. Single-value constraints require that a

Topic 7 Database Design Methodology LEARNING OUTCOMES When you have completed this Topic you should be able to: 1. Discuss the purpose of a design methodology. 2. Explain three main phases of design methodology.

UNIT -2 Entity-Relationship Model Introduction to ER Model ER model is represents real world situations using concepts, which are commonly used by people. It allows defining a representation of the real

Tore Risch Uppsala University, Sweden UDBL Basic Database Concepts What is a database? A database is a collection of related data stored in a computer managed by a DBMS. What is a DBMS, Database Management

Entity-Relationship Model Database Modeling (Part 1) A conceptual data model, which is a representation of the structure of a database that is independent of the software that will be used to implement

XV. The Entity-Relationship Model The Entity-Relationship Model Entities, Relationships and Attributes Cardinalities, Identifiers and Generalization Documentation of E-R Diagrams and Business Rules The

E-R Diagram Database Development We know how to query a database using SQL A set of tables and their schemas are given Data are properly loaded But, how can we develop appropriate tables and their schema

GROUP 2 PRACTICE EXAMPLES FOR THE REVIEW QUIZ: Review Quiz will contain very similar question as below. Some questions may even be repeated. The order of the questions are random and are not in order of

Fundamentals of Database System Chapter 4 Normalization Fundamentals of Database Systems (Chapter 4) Page 1 Introduction To Normalization In general, the goal of a relational database design is to generate

Database Systems I (Compulsory) INTRODUCTION This is one of the 4 modules designed for Semester 2 of Bachelor of Information Technology Degree program. CREDITS: 04 LEARNING OUTCOMES On completion of this

Mapping Data Models to a Relational Database Time to dovetail two prior topics: data modeling (E-R, UML) and the relational data model (structure, algebra) To recap, this is how they relate: The data model

Introduction Database Management Systems Database Management System (DBMS) Collection of interrelated data and set of programs to access the data Convenient and efficient processing of data Database Application

Component 4: Introduction to Information and Computer Science Unit 6: Databases and SQL Lecture 2 This material was developed by Oregon Health & Science University, funded by the Department of Health and

Database Design Database Design Adrienne Watt Port Moody Except for third party materials and otherwise stated, content on this site is made available under a Creative Commons Attribution 2.5 Canada License.

The Entity-Relationship Model 221 After completing this chapter, you should be able to explain the three phases of database design, Why are multiple phases useful? evaluate the significance of the Entity-Relationship

Lecture 6 SQL, Logical DB Design Relational Query Languages A major strength of the relational model: supports simple, powerful querying of data. Queries can be written intuitively, and the DBMS is responsible

Review: Participation Constraints Does every department have a manager? If so, this is a participation constraint: the participation of Departments in Manages is said to be total (vs. partial). Every did

The Entity- Relationship Model R &G - Chapter 2 A relationship, I think, is like a shark, you know? It has to constantly move forward or it dies. And I think what we got on our hands is a dead shark. Woody

Database Design Methodology Three phases Database Design Methodology Logical database Physical database Constructing a model of the information used in an enterprise on a specific data model but independent

The Entity-Relationship (ER) Model (Study Cow book Chapter 2) Comp 521 Files and Databases Spring 2010 1 Overview of Database Design Conceptual design: (ER Model is used at this stage.) What are the entities

The Entity-Relationship Model Chapter 2 Instructor: Vladimir Zadorozhny vladimir@sis.pitt.edu Information Science Program School of Information Sciences, University of Pittsburgh 1 Database: a Set of Relations