• Week 02
• Email and Context
• n:m:p Relationships
• Data Models (recap)
• Mapping ER to SQL
• SQL Schemas (recap)
• SQL Types
• SQL Constraints
• Exercise: Constraints
• Exercise: ER-to-SQL Mapping
• Exercise: ER-to-SQL Mappings
• Mapping Strong Entities
• Mapping Weak Entities
• Mapping N:M Relationships
• Mapping 1:N Relationships
• Mapping 1:1 Relationships
• Mapping n-way Relationships
• Exercise: Mapping n-way Relationships
• Mapping Composite Attributes
• Mapping Multi-valued Attributes (MVAs)
• Mapping Subclasses
• Exercise: Class Hierarchy ER-to-SQL

❖ Week 02

In today's lecture ...

• SQL Data Definition Language (DDL)
• Mapping ER → Relational/SQL

Things to do ...

• Tutorials start this week
• Quiz due Friday midnight
• Assignment 1 out later in the week
• Set up your PostgreSQL server
  
  • ~380/730 students have logged in to vxdb02 and have /localstorage
  • come to a Help Session if you're having trouble installing PostgreSQL

❖ Email and Context

When sending email to us (cs3311), please include your zID

Before typing commands, think about the context:

Prompt		Context
$		in Linux shell run Unix commands, e.g. ls, cd
db=#		in psql run SQL commands, e.g. select, update
db>		in sqlite3 run SQL commands, e.g. select, update
none		in vim,   you are doomed but try :q or ZZ

❖ n:m:p Relationships

Two possible ER models for "prescribes" in the medical scenario

Could be done as a 3-way relationship, or using a new entity

❖ Data Models (recap)

Entity-relationship (ER) data model

• attributes,   entities,   relationships,   subclasses
• relationship variations: total/partial, n:m, 1:n, 1:1

Relational data model

• attributes,   tuples,   relations
• attribute = name + domain/type
• tuple = list of attributes,  based on attribute definitions
• relation = set of tuples,  based on a tuple definition

E.g.   Student(zID:integer, name:string, WAM:float, ...)

❖ Mapping ER to SQL

Above example gives informal description of relational schema

Need a more formal way of describing relational schemas

SQL data definition language (DDL) provides this

SQL is an implementation of relational data model

• relations → tables,   tuples → tuples,   attributes → columns/fields

Mapping ER to SQL

• entity sets → tables,   entities → rows/tuples,   attributes → columns/fields
• relationships → tables  or  foreign keys
• multi-valued-attributes/weak-entities/subclasses → ?

❖ SQL Schemas (recap)

Primary SQL DDL construct is table creation:

create table TableName (
   attr1Name  type [constraints],
   attr2Name  type [constraints],
   attr3Name  type [constraints],
   ...
   primary key (attrxName ),
   foreign key (attryName)
               references OtherTable (attrzName )
);

SQL schema = collection of table definitions, including constraints

❖ SQL Types

Built-in types

• numbers: integer, numeric(n), real
• strings: char(n), varchar(n), text
• time: date, time, timestamp, interval
• boolean, monetary, geometric, enumerated, ...

Make your own

create domain Name as Type Constraint;
create type Name as enum (val1, val2,...);

❖ SQL Constraints

Constraints in SQL DDL

• on attributes e.g.   integer,   check (x > 0),   not null
• on attributes e.g.   text   can use regular expressions
• on table e.g.   unique,   primary key (a,b,c)
• between tables e.g.   foreign key (x) references T(y)

Tuples which do not satisfy constraints cannot be added to DB

Gives strong guarantee that the data is valid (internally consistent)

But does not guarantee that it reflects reality

❖ Exercise: Constraints

Constraint = SQL expression limiting possible values

Define type + constraints for

• positive integers
• marks (range 0..100)
• unsw course codes (e.g. COMP3311)
• unsw term codes (e.g. 25T1)
• student IDs (e.g. z5432123)
• person's name (alpha + space + - + ')
• colours (e.g. red, green, blue)
• grades (e.g. HD, DN, CR, PS, FL, UF)

❖ Exercise: ER-to-SQL Mapping

Describe this ER model as an SQL schema

Use domain definitions to make types more precise.

Use integer id attributes for all entity tables.

❖ Exercise: ER-to-SQL Mappings

For each of the following ER-to-relational mappings

• give an SQL rendering of the relational version
• choose obvious and simple domains for attributes

❖ Mapping Strong Entities

An entity set E  with atomic attributes  a₁, a₂, ... a_n

maps to

A relation R  with attributes (columns)  a₁, a₂, ... a_n

Example:

Note: the key is preserved in the mapping.

❖ Mapping Weak Entities

Example:

❖ Mapping N:M Relationships

Example:

❖ Mapping 1:N Relationships

Example:

❖ Mapping 1:1 Relationships

Example:

❖ Mapping 1:1 Relationships (cont)

If there is no reason to favour one side of the relationship ...

❖ Mapping n-way Relationships

Relationship mappings above assume binary relationship.

If multiple entities are involved:

• n:m generalises naturally to n:m:p:q
  • include foreign key for each participating entity
  • include any other attributes of the relationship
• other multiplicities (e.g. 1:n:m) ...
  • need to be mapped the same as n:m:p:q
  • so not quite an accurate mapping of the ER

❖ Exercise: Mapping n-way Relationships

Convert the following ER models into SQL schemas:

❖ Mapping Composite Attributes

Composite attributes are mapped by concatenation or flattening.

Example:

❖ Mapping Multi-valued Attributes (MVAs)

MVAs are mapped by a new table linking values to their entity.

Example:

❖ Mapping Subclasses

Three different approaches to mapping subclasses to tables:

• ER style
  • each entity becomes a separate table,
  • containing attributes of subclass + FK to superclass table
• object-oriented
  • each entity becomes a separate table,
  • inheriting all attributes from all superclasses
• single table with nulls
  • whole class hierarchy becomes one table,
  • containing all attributes of all subclasses (null, if unused)

Which mapping is best depends on how data is to be used.

❖ Mapping Subclasses (cont)

Example of ER-style mapping:

❖ Mapping Subclasses (cont)

Example of object-oriented mapping:

❖ Mapping Subclasses (cont)

Example of single-table-with-nulls mapping:

❖ Exercise: Class Hierarchy ER-to-SQL

Convert the following class hierarchy to SQL using ER mapping: