Home > computing > COMP3311 Wk02

COMP3311 Wk02

Various things about mapping ER Designs to Relational Schemas.

Mapping Strong Entities

strongent

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

mapnnrel

Mapping 1:N Relations

map1nrel2Mapping 1:1 Relations

map11rel

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.

Advertisements
Categories: computing Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

I don't read comments anymore due to an increase in spam comments. If you want to get in touch please send me an email (see tianjara.net for details).

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: