avatar tianjara.net | blog icon Andrew Harvey's Blog

COMP3311 Wk02
26th March 2009

Various things about mapping ER Designs to Relational Schemas.

Mapping Strong Entities


The relational model supports only atomic attributes. To map composite attributes you can try,

  1. Concatenate the attributes eg. Struct name {"John", "Smith"} --> "John Smith"
  2. Map atomic components of the composite attribute to a set of atomic components. eg. mapcompatt1 mapcompatt_table
  3. ??

Mapping N:M Relations


Mapping 1:N Relations

map1nrel2Mapping 1:1 Relations


Notes from the Text Book (The Lecture Notes are a Little Different)

Domain Types & User Types

In the sample code for the first assignment to define "custom types" create domain is used. eg.

create domain PersonGender as char(1) check (value in ('M','F'));

However the text also shows create type. eg.

create type Dollars as numeric(12,2) final

It goes on to explain the difference.

Pattern Matching

Patterns in SQL can be desribed using % and _.


select foo from bar where lar like '_to%'

This will match to any of these strings, "Lto" "Ato" "ltoo" "rtoto" ... (any character at the start, then the "to" string, then any (even null) trailing string)

You can define the escape character for a like comparison as follows,

like 'he\%%'  escape '\'' --matches all strings begining with 'he%'

You can also use not like.

SQL:1999 allows for similar too which is similar to Unix regular expressions.

Drop vs. Delete

drop table r will remove all the tuples from r, and removes the schema of r, whereas

delete from r will just remove all the tuples from r, but leaving the schema so you can still add values to the table.


Shepherd, John. COMP3311 09s1 Lecture Slides. http://www.cse.unsw.edu.au/~cs3311/09s1/lectures/. (Diagrams have also been sourced from here).

Silberschatz. Database System Concepts. 5th Ed.

Tags: comp3311, computing.