tianjara.net | Andrew Harvey's Blog

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.
3. ??

Mapping N:M Relations

Mapping 1:N Relations

Mapping 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.

• Domains can have constraints (such as not null) specified on them, as well as default values defined on the domain type. You can't do this with user defined types.
• Domains are not strongly typed. Hence you can assign values of one domain type to values of another domain type so long as the underlying types are compatible.

Pattern Matching

Patterns in SQL can be desribed using % and _.

• Percent (%): The % character matches any substring.
• Underscore (_): The _ character matches any character.

eg.

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.

References

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.