Translating E/R Diagrams into Relational Schemas

1. Translating E/R Diagrams into Relational Schemas

2. Translating Entity Sets • Include all attributes of the entity set and use the entity set’s key as the primary key. • Example: The Author entity set CREATE TABLE Authors (name CHAR(30), phone CHAR(20), address CHAR(20), DOB DATE, PRIMARY KEY (name, phone))

3. Translating M-N Relationship Sets CREATE TABLE Wrote( isbn CHAR(11), name CHAR(30), phone CHAR(20), done DATE, PRIMARY KEY (isbn, name, phone), FOREIGN KEY (isbn) REFERENCES Books (isbn), FOREIGN KEY (name, phone) REFERENCES Authors (name, phone)) • Attributes of the relation must include: • Keys for each participating entity set (as foreign keys). • This set of attributes forms a key for the relation. • All descriptive attributes. • Ignore participation constraints for now!!

4. Translating M-1 or 1-M Relationship Sets • Key of unconstrained entity goes into relation for entity with the key constraint • Descriptive attributes of relationship also go into relation for entity with the key constraint • Example: Books table and “publish” relationship: CREATE TABLE Books ( isbn CHAR(11), title CHAR(20), pubname CHAR(25), pubdate DATE, PRIMARY KEY (isbn), FOREIGN KEY (pubname) REFERENCES Publishers (name))

5. Translating 1-1 Relationship Sets • Key of one entity goes into relation for other entity, along with the descriptive attributes of relationship • Choice is up to DBA and is application-dependent • Example: Publishers, Editors tables and “edits” relationship: CREATE TABLE Editors ( name CHAR(30), phone CHAR(20), pubname CHAR(25), hours INTEGER, PRIMARY KEY (name), FOREIGN KEY (pubname) REFERENCES Publishers (name) ) CREATE TABLE Publishers ( name CHAR(25), address CHAR(20), URL CHAR(50), PRIMARY KEY (name))

6. Participation Constraints in SQL • Capture using NOT NULL if: • Entity with the participation constraint also has a key constraint for the same relationship AND • Relation for the constrained entity contains a foreign key for the related entity. • Examples: • Books-Publish participation constraint: • CREATE TABLE Books (….pubname CHAR(25) NOT NULL, ….) • Authors-Wrote participation constraint: • Needs an ASSERTION or TRIGGER (later in course… needs SQL!) • Publisher-Edits participation constraint: • Re-design Publishers/Editors and use NOT NULLOR use an ASSERTION or TRIGGER

7. Translating Weak Entity Sets • Weak entity set and identifying relationship set are translated into a single table. • When the owner (strong) entity is deleted, all owned weak entities must also be deleted. CREATE TABLE Dependents ( aname CHAR(30), dname CHAR(20), age INTEGER, PRIMARY KEY (aname, dname), FOREIGN KEY (aname) REFERENCES Authors(name), ON DELETE CASCADE)

8. 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 for efficient evaluation. • The key: precise semantics for relational queries. • Allows the optimizer to extensively re-order operations, and still ensure that the answer does not change.

9. The SQL Query Language • To find all 18 year old students, we can write: SELECT * FROM Students WHERE age=18 • To find just names and logins, replace the first line: SELECT name, login

10. Querying Multiple Relations • What does the following query compute? SELECT S.name, E.cid FROM Students S, Enrolled E WHERE S.sid=E.sid AND E.grade=“A” Given the following instance of Enrolled: we get:

11. E/R to Relations: Summary • Entity set Relation • M-N Relationship Relation (keys of related entities plus relationship attributes) • 1-M or M-1 Relationship: Table for entity with key constraint gets key of other table plus relationship attributes • 1-1 Relationship: Table for one entity gets key of the other plus relationship attributes • Powerful and natural relational query languages exist. • The resultant relational schema may have some nasty properties…. We’ll soon learn how to fix these.