Databases: Mandatory assignment 1: Relational database

In this assignment you must model and implement a relational database.

You must use data from the web site http://www.ratebeer.com/

You don't have to model and implement all the data behind the web site.

Entity-Relationship diagram

Make an Entity-Relationship (ER)diagram.

The diagram must include at least

• one one-to-many relationship
• one many-to-many relationship
• one multi-valued attribute (if you cannot find on you should "invent" one)

Use pen and paper for the diagram. Fancy diagramming tools are not necessary.

Map to the relational model

Map your ER diagram to the equivalent relational model.

Primary keys and foreign keys must be visible in your relational model.

Use pen and paper for the diagram. Fancy diagramming tools are not necessary.

Normalization

Show the functional dependencies in you relational model.

Show that all your relations are properly normalized (up to Boyce-Codd normal form)

Create tables

Create tables from your relationions, use Oracle 11g

Use

• proper data types
• primary keys, must be ID's coming from a sequence
• foreign keys
• maybe other constraints, like unique, check, etc.
• regarding rate, hint: virtual column might be useful,
  a new feature of Oracle 11g, http://oracle-base.com/articles/11g/virtual-columns-11gr1.php

Create view

Create at least one view

Insert data

Insert some data into the tables.

Each table must have at least 5 rows.

Select

Do some interesting select statements, including

• functions
• joins, inner and outer
• sub-queries

Stored procedures / functions

Program at least one stored procedure or function.

Index

Add at least one index to your database