COMP3311 24T2

Week 03
ER→Rel Mapping, SQL DDL, ER→SQL Mapping

We have adopted (and violated) several conventions in the SQL schemas in these questions.

We have suggested that all tables derived from entities should have pluralised names (e.g Student in ER, Student in relational box-and-arrows, Students in SQL). Our examples and solutions don't always do this.
We have suggested not using all upper-case for SQL keywords. Many of our solutions use upper-case keywords.
We have suggested that primary and foreign keys always be defined after the attributes, despite the fact that there are more compact ways of defining them. We sometimes use the compact versions of key definitions.

Note that all of the above are style conventions. Not doing them will not make an SQL "program" incorrect, but mixing them in a single *.sql file is bad style. We would, however, prefer that you adopt our suggestions.

Why is it useful to first do an ER design and then convert this into a relational schema?
Convert each of the following ER design fragments into a relational data model expressed as a box-and-arrow diagram:
In the mapping from the ER model to the relational model, there are three different ways to map class hierarchies (ER, OO, single-table). Show each of them by giving the mapping for the following class hierarchy:

Use box-and-arrow diagrams for the relational models.
Now consider a variation on the above class hierarchy where the sub-classes are disjoint. Show the three possible mappings for the class hierarchy and discuss how effectively they represent the semantics of the disjoint sub-classes:

Use box-and-arrow diagrams for the relational models.
Consider the following two relation definitions:
For each, show the possible ways of defining the primary key in the corresponding SQL create table statement.

Give examples of constraints that
1. apply to an individual attribute
2. apply across a whole table
3. apply between tables

What is the difference between the following two ways to define a primary key?

create table R (                    create table R (
   a integer primary key,              a integer,
   b integer,                          b integer,
   ...                                 ...
);                                     primary key (a)
                                    );

Discuss suitable SQL representations for each of the following attributes, including additional domain constraints where relevant:
1. people's names
2. addresses
3. ages
4. dollar values
5. masses of material

In many real PostgreSQL schemas, you will see definitions like
```
create table R (
   id    serial,
   name  text,
   d_o_b date,
   ...
   primary key (id)
);
```
1. What is the effect of the serial declaration?
2. How would you make use of it when inserting tuples?
3. How would you reference R.id as a foreign key?

Convert the following entity into an SQL CREATE TABLE definition:

Give reasons for all choices of domain types.
Convert the following entity into an SQL CREATE TABLE definition:

Give reasons for all choices of domain types.
Convert the following ER design into a relational data model:

You can assume that each attributes contains (at least) a suitably-named attribute containing a unique identifying number (e.g. the Lecturer entity contains a LecID attribute).
Convert the following ER design into an SQL schema:

Which elements of the ER design do not appear in the relational version?
Convert the following ER design into a relational data model expressed first as a box-and-arrow diagram and then as a sequence of statements in the SQL data definition language:

Which elements of the ER design do not appear in the relational version?
[Based on GUW 2.1.3] Convert the following ER design into a relational data model expressed first as a box-and-arrow diagram and then as a sequence of statements in the SQL data definition language:

Which elements of the ER design do not appear in the relational version?
Convert the following ER design into a relational data model expressed first as a box-and-arrow diagram and then as a sequence of statements in the SQL data definition language:

Which elements of the ER design do not appear in the relational version?
Convert the following ER design to relational form:

Which elements of the ER design do not appear in the relational version?
Using this version of the Person class hierarchy, from the Medical scenario described previously, convert the ER design to relational form as an SQL schema:

Give mappings using both the ER style and single-table-with-nulls style.
Using the Person class hierarchy from the previous question, convert this ER design for the medical scenario into relational form:

Assume that the Person classes are mapped using the ER-style mapping. Which elements of the ER design do not appear in the relational version?
Convert this ER design for the book publishing scenario into an SQL schema:

Give two versions, one using the ER-style mapping of subclasses, and the other using single-table-with-nulls mapping of subclasses.