





















Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
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
1 / 29
This page cannot be seen from the preview
Don't miss anything!
David Toman
School of Computer Science University of Waterloo
Introduction to Databases CS
How to obtain a good relational database schema
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
Weak entity set E! table E
Columns of table E should include
Primary key of weak entity set! primary key of table
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
3 Otherwise: relationship set R! table R
Columns of table R should include
Primary key of table R determined as follows
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
Course CourseNum CourseName
Student StudentNum StudentName
Section CourseNum SectionNum ProfNum
EnrolledIn CourseNum SectionNum StudentNum Mark
Professor ProfNum ProfName
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
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
Example:
LicenceNum Vehicle Price
MakeAndModel
Truck Car MaxSpeed
PassengerCount
Tonnage
AxelCount
LicenceNum MakeAndModel Price MaxSpeed PassengerCount
Car
LicenceNum MakeAndModel Price Tonnage AxelCount
Truck
create table DEPT ( ID integer not NULL, DeptName char(20), MgrNO char(3), PRIMARY KEY (ID) )
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=