Introduction to Database Systems EECS3421B
York University Fall 2019 Project 2
In this project, you are to
1. Create the relational schema and tables based upon an ER design which we are providing Figure 1.
The Conceptual Design
This conceptual design is a simplified ER schema from Project 1 for the CIPA domain.
Figure 1
1Page
Part A The Relational Schema
Translate the above ER diagram Figure 1 into an equivalent relational model.
Specify the relational schema of each entity
Specify the primary key on each relation
Specify any foreign key references to other relations
Choose appropriate domain types integer, date, varchar…, etc. as is appropriate, or as when dictated below. Key attributes and ones in italics in the ER diagram should be declared as not nullable.
For attribute and relation names, carry over those from the ER diagram where you can, and make sensible choices where you cannot.
Mapping ERD to Relational Schema
Strong EntitySets
Create a relational schema with the same name as your entities E for each entity set and with the same attributes A.
Primary key of the relation is the same as the primary key of the corresponding entity set. RelationshipSets
Entity sets participating in a relationship are strong entity sets: The attributes of relationship R include:
o Manytomany relationships
The union of participating entity sets primary keys and the relationship
attributes if they exist.
The primary key of the relationship is composed of the primary keys of
the participating entity sets in that relationship.
o Onetoone relationships
Either primary key of the participating entity sets can be the primary key. The union of participating entity sets primary keys and the relationship
attributes if they exist.
o Manytoone onetomany
The union of participating entity sets primary keys and the relationship
attributes if they exist.
The primary key of the relationship R is the primary key of the entity set
participating in the many side of the relationship.
2Page
Foreign Keys in relationship sets: each relationship set associated with participating entity sets has to have referential integrity constraints foreign keys when are mapped to a relation R.
o Foreachparticipatingentityset,theprimarykeyattributefromtheentityset Ei is the foreign key in the relation R.
o Example:
E1 a1, a2, a3, a4, a5 E2 b1, b2, b3
R ca1, cb1, c1, c2
FK ca1 refs E1 a1 FK cb1 refs E2 b1
Weak entitysets: Each weak entity set has at least one supporting strong entity set. The attributes of a weak entity set is the union of the primary key attributes in the strong entity set and the attributes in the weak entity.
o If the weak entity set has any key, the primary key of the weak entity is composed of its key and the primary key from the strong entity.
o The primary key attributes from the strong entity set is the foreign key in the weak entity set.
o Example 1:
E a1, a2, a3, a4, a5strong entity set
E a1, b1, b2, b3weak entity set with a key b1
FK a1 refs E1 a1 o Example 2:
E a1, a2, a3, a4, a5strong entity set
E a1, b1, b2, b3weak entity set without a key
FK a1 refs E1 a1
o Example 3 a weak entity set E with two supporting entity sets E1 and E2:
E1 a1, a2, a3, a4, a5strong entity set
E2 c1, c2, c3, c4strong entity set
E a1, c1, b1, b2, b3weak entity set with a key b1
FK a1 refs E1 a1 FK c1 refs E2 c1
Subclass to Relation isa
There are three approaches to translate a sub class in ERD to a relation. o ObjectOriented
Each sub class entity set is mapped to a relation.
The attributes in the sub class relation is the union of the super class entity
set attributes and the attributes from the sub class entity set.
Exmaple:
P p1, p2, p3, p4super class
C p1, p2, p3, p4, c1, c2sub class, c1 and c2 are the sub class attributes
3Page
o Use Nulls
You have only one relation S for the super class entity set and the subclass
entity sets. The attributes of the relation is the union of all attributes from the super class entity set and the sub class entity sets. Tuples in the relation will have null values for attributes that do not belong to them.
Example:
S p1, p2, p3, p4, c1, c2
p1, p2, p3, and p4 are the super class attributes c1 and c2 are the sub class attributes
o ER Style
Each sub class entity is mapped to a relation. The sub class relation
attributes is the union of key attributes from the super class entity set and
all attributes of the sub class entity set. Example:
P p1, p2, p3, p4super class
C p1, c1, c2sub class, c1 and c2 are the sub class attributes and p1 is the
super class enity primary key
Combining Relations
Combining relations has pros and cons:
Proos
o
Cons o
Space efficiency: using less storage.
Performance improvement: Eliminating foreign keys and joins.
Using Null values: This can be problematic if the relation contains many null values.
Manytoone Relationship Sets
The participation of the entity set from the many side of the relationship is total.
o If a relationship set R between two entity sets E1 and E2 is manytoone and E1
participation is total, the entity set from the many side E1 of the relationship can
be combined by R.
o The primary of R is the union of E1 and E2 primary keys before combining the
relation R and E1.
o The primary key of the combined relation is the same as the primary key of
relation E1.
4Page
The participation of the entity set from the many side of the relationship is not total.
o If a relationship set R between two entity sets E1 and E2 is manytoone and E1
participation is not total, the entity set from the many side E1 of the relationship
can still be combined by R.
o The primary key of R is the union of E1 and E2 primary keys before combining
the relation R and E1.
o The primary key of the combined relation is the same as the primary key of
relation E1.
o Since the E1 participation is not total, for some tuples the null values will be
stored for the attributes from the relation R. Onetoone Relationship Sets
The onetoone relationship R can be combined with either participating entity sets E1 or E2. o After combing the relation R with relation E1 for instance, the primary key of E1
will be the primary key of the combined relation.
Relationship of a Weak Entity Set and the Supporting Entity Set
The relationship R between a weak entity set E and a strong entity set E is many to one.
The week entity participation in the relationship R is total.
The weak entity relationship R can be combined with the weak entity set E from many
side.
Part B The Relational Schema SQL
Translate the relational schema in the relational model from Part A into an equivalent relational schema in SQL the data definition language, DDL. Do not create any tables that are not needed. Declare primary keys and foreign keys per table appropriately to capture the logic of the ER diagram and the relational model correctly.
Choose appropriate domain types integer, date, varchar…, etc. Key attributes and ones in italics in the ER diagram should be declared as not nullable.
For attribute and table names, carry over those from the ER diagram where you can, and make sensible choices where you cannot.
5Page
Examples
Raccoon Rapsody Database
As an example of an SQL schema script, see rrdbcreate, the SQL schema script for the RaccoonRhapsody database from EECS3421A in fall 2016. This database was used for the project to write SQL queries.
Note that this file has both the schema and the data in the same file; you are asked to put these in separate files for your schema and data, however.
The file rrdbdrop is a simple file of SQL drop commands that clears out the RRDB database. You may find you want to make a drop file too for your Episode database, as you will likely find the project is quite iterative.
Quest Schema
Project 2, Creating the Quest MMORPG Database, was the equivalent project to this one from EECS3421A in fall 2016. You can look it over as an exercise, and as an example for doing this project.
Note that the ER in that project is a simplified schema from what the class did for their ER design Project 1A: Project 1: Loot! And the RRDB database was then a variation on this.
Solutions to the Quest Project:
questschema
questdrop
questdata
questquery
questanswer Deliverable
Submit
o An electronic copy of Part A of your project schema.pdf in PDF. Note that submission of anything but PDF will be rejected!
o An electronic copy of Part B of your project create.sql in PDF. Note that submission of anything but .sql will be rejected!
6Page
The create.sql script is a text file with .sql extension containing a sequence of SQL DDL statements to create your database tables and constraints in DB2.
You also need to hand in a printed version of both Part A and B of the project as your project report.
Online Submission Due: by 11:59pm Monday 28 October 2019. In Class Submission Due: 10:00am Tuesday 29 October 2019. Your project report should include the following.
Cover Page
A cover page should have your name and student, and should indicate it is for the ER Project of EECS3421B for Fall 2019.
Part A Relational Schema
Your relations, primary keys, and foreign keys mapped from the given ER.
Part B SQL statements
Your DDL statement to create all tables and their constraints from Part A.
Documentation optional
Paragraphs explaining details about the project.
o Any clarifications about your relational schema and constraints.
o Any constraints business rules apparent from the requirements that you are
unable to specify using the relational model in Part A. o Include the notes in your pdf submission.
Your project must be typeset; that is, no handwriting submission. The cover page for submitting your work should look something as follows.
Student:
Sur Family Name: Given Name:
Class:
Term:
Project:
EECS3421B
Fall 2019 Relational Schema
Call your PDF file schema.pdf. Submit online your schema.pdf and create.sql files as follows. submit 3421B p2 schema.pdf create.sql
7Page