Logical Database Design Lecture 11 1 Main Topics

Logical Database Design Lecture 11 1

Main Topics • • • 2 Logical Database Design Relational Database Model Transforming E-R Diagrams into Relations Represent entities Represent relationships

Logical Database Design In Logical Database Design: - Translation of ER diagram to a relational database model. ER diagram Translated to Relational Database Model. 3

Relational Database Model • The relational database model: represents data in the form of related tables or relations. • A relation: is a named, two-dimensional table of data. • Each relation consists of a set of named columns and an arbitrary number of unnamed rows. 4

5

6

Transforming E-R Diagrams into Relations • It is useful to transform the conceptual data model into a set of relations. • Steps – Represent entities. – Represent relationships. 7

Representing Entities • Each regular entity is transformed into a relation. • The entity set label is translated into a relation name. • The identifier of the entity set becomes the primary key of the corresponding relation. • Each attribute of the entity set corresponding to column in a relation. • Each row of a relation corresponds to a record (tuple) that contains data values for an entity. 8

Logical DB Design: ER to Relational ssn name 123 -22 -3666 Attishoo • Entity sets to tables. ssn name 231 -31 -5368 Smiley salary 4800 2200 131 -24 -3650 Smethurst 3500 salary Employees 9

Representing Relationships • The procedure for representing relationships depends on both the degree of the relationship – unary, binary, ternary – and the cardinalities of the relationship. – Since binary relationship is the Most common type of relationship types encountered in data modeling. – We represent the binary relation with different cardinalities. 10

Binary 1: 1 Relationship • Binary 1: 1 Relationship is represented by any of the following choices: – Add the primary key of A as a foreign key of B. – Add the primary key of B as a foreign key of A. 11

Binary 1: N Relationship • Binary 1: N Relationship is represented by adding the primary key attribute (or attributes) of the entity on the one side of the relationship as a foreign key in the relation that is on the many side of the relationship. 12

Key Constraint in ER Diagram ST-name ST-id D-name ST-phone Students ST-GPA D-id Belongs To D-location Departments ST-Birthday Many-to-one relationship: no need to be implemented 13 as a table!

Students ssn name phone did 1111 John 617 -373 -5120 1 2222 Alice 781 -322 -6084 1 3333 Victor 617 -442 -7798 3 Departments did dname address 1 Computer Science #161 Cullinane 2 Electrical Engineering #300 Egan 3 Physics #112 Richard 14

Binary M: N Relationships • Create another relation and include primary keys of all relations as primary key of new relation 15

Example of ER Diagram time name ssn title phone Students unit cid Enroll Courses To implement the above design, store three tables in the database. 16

Students ssn name Enroll phone 1111 John 617 -373 -5120 2222 Alice 781 -322 -6084 3333 Victor 617 -442 -7798 ssn Courses cid title unit CSU 430 Database Design 4 CSG 131 Transaction Processing 4 CSG 339 Data Mining 4 17 cid time 1111 CSU 430 Fall’ 03 1111 CSG 339 Spring’ 04 2222 CSG 131 Winter’ 03 2222 CSG 339 Spring’ 04 3333 CSU 430 Winter’ 01