Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Entity-relationship-Introduction to Database Management-Lecture 04 Slides-Computer Science, Slides of Introduction to Database Management Systems

Entity-relationship to Relational Tables, Database Design, E-R Diagram, Relational Schema, E-R Diagram to Relational Schema, Strong Entity Sets, Weak Entity, Representing Relationship Sets, ER Diagram, Aggregation, Specialization, Integrity Constraints, Foreign Keys, Constraints, Data Control Language

Typology: Slides

2011/2012

Uploaded on 02/12/2012

richiey
richiey 🇨🇦

32 documents

1 / 29

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Entity-Relationship to
Relational Tables
David Toman
School of Computer Science
University of Waterloo
Introduction to Databases CS348
David Toman (University of Waterloo) ER to Relational 1 / 28
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d

Partial preview of the text

Download Entity-relationship-Introduction to Database Management-Lecture 04 Slides-Computer Science and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Entity-Relationship to

Relational Tables

David Toman

School of Computer Science University of Waterloo

Introduction to Databases CS

Database Design

How to obtain a good relational database schema

  • (^) Deriving new relational schema from ER-diagrams
  • Normal forms: use of constraints in evaluating existing relational schema

Representing Strong Entity Sets

Entity set E with attributes a 1 ; : : : ; an table E with attributes a 1 ; : : : ; an

Entity of type E $ row in table E Primary key of entity set! primary key of table Example: Student

StudentNum StudentName

Major

Student StudentNum StudentName Major

Representing Weak Entity Sets

Weak entity set E! table E

Columns of table E should include

  • Attributes of the weak entity set
  • (^) Attributes of the identifying relationship set
  • Primary key attributes of entity set for dominating entities

Primary key of weak entity set! primary key of table

Representing Relationship Sets

1 If the relationship set is an identifying relationship set for a weak entity set then no action needed

2 If we can deduce the general cardinality constraint (1,1) for a component entity set E then add following columns to table E

  • Attributes of the relationship set
  • Primary key attributes of remaining component entity sets

3 Otherwise: relationship set R! table R

Representing Relationship Sets (cont.)

Columns of table R should include

  • Attributes of the relationship set
  • (^) Primary key attributes of each component entity set

Primary key of table R determined as follows

  • (^) If we can deduce the general cardinality constraint (0,1) for a component entity set E , then take the primary key attributes for E
  • Otherwise, choose primary key attributes of each component entity

Example Translation: ER diagram

Course

SectionOf

Section

CourseNum

TaughtBy

Professor

EnrolledIn

Student

ProfNum

ProfName StudentNum

StudentName

Mark

CourseName

SectionNum

1

N (^) N

1

(6, 50)

N (3, 5)

(1, 1)

(0, N)

(1, N)

(1, 1) N

Example Translation: result

Course CourseNum CourseName

Student StudentNum StudentName

Section CourseNum SectionNum ProfNum

EnrolledIn CourseNum SectionNum StudentNum Mark

Professor ProfNum ProfName

Representing Aggregation (cont.)

Example:

Student EnrolledIn Course

CourseAccount

Account UserId

StudentNum

CourseNum

ExpirationDate

N N

1

1

Student StudentNum

Course CourseNum

Account UserId EnrolledIn StudentNum CourseNum CourseAccount UserId StudentNum CourseNum ExpirationDate

Representing Specialization

Create table for higher-level entity set, and treat specialized entity subsets like weak entity sets

Example: Student

Graduate

Degrees

SupervisedBy Professor

StudentName

StudentNumber

ProfessorName

1 (1, 1) (0, N) N

Student StudentNumber StudentName

Professor ProfessorName Graduate StudentNumber ProfessorName Degree StudentNumber Degree

Representing Generalization (cont.)

Example:

LicenceNum Vehicle Price

MakeAndModel

Truck Car MaxSpeed

PassengerCount

Tonnage

AxelCount

LicenceNum MakeAndModel Price MaxSpeed PassengerCount

Car

LicenceNum MakeAndModel Price Tonnage AxelCount

Truck

Defining Relations and Integrity Constraints in SQL

  • connected with a table (definition) ) Primary Keys ) Foreign Keys ) CHECK constraints
  • separate ECA rules (triggers)

Example

create table DEPT ( ID integer not NULL, DeptName char(20), MgrNO char(3), PRIMARY KEY (ID) )

Example (cont.)

sql => insert into DEPT values
sql (cont.) => ( 1 ,’Computer Science’, 000100) DB20000I The SQL command completed successfully.

sql => insert into DEPT values
sql (cont.) => ( 1 ,’Computer Science’, 000100) SQL0803N One or more values in the INSERT or UPDATE statement are not valid because they would produce duplicate rows for a table with a unique index. SQLSTATE=