J. Correia Lopes

FEUP/DEI & INESC TEC

User Tools

Site Tools


teach:lbaw:medialib:a5

A5: Relational schema, validation and schema refinement

This artifact contains the Relational Schema obtained by mapping from the Conceptual Data Model. The Relational Schema includes the relation schema, attributes, domains, primary keys, foreign keys and other integrity rules: UNIQUE, DEFAULT, NOT NULL, CHECK.

1. Relational Schema

Relation schemas are specified in the compact notation:

R01 user(id, email UK NN, name NN, obs, password NN, img, is_admin NN)
R02 author(id, name NN, img)
R03 collection(id, name NN)
R04 work(id, title NN, obs, img, year NN CK year > 0, id_user → user NN, id_collection → collection)
R05 author_work(id_author → author, id_work → work)
R06 nonbook(id_work → work, type NN CK type IN Types)
R07 publisher(id, name NN)
R08 book(id_work → work, edition, isbn UK NN, id_publisher → publisher)
R09 location(id, name NN, address NN, gps)
R10 item(id, id_work → work NN, id_location → location NN, code NN, date NN DF Today)
R11 loan(id, id_item → item NN, id_user → user NN, start NN, end NN CK end > start)
R12 review(id_user → user, id_work → work, date NN DF Today, comment NN, rating NN CK rating > 0 AND rating < = 5)
R13 wish_list(id_user → user, id_work → work)

where UK means UNIQUE KEY, NN means NOT NULL, DF means DEFAULT and CK means CHECK.

2. Domains

Specification of additional domains:

Today DATE DEFAULT CURRENT_DATE
Types ENUM ('CD', 'DVD', 'VHS', 'Slides', 'Photos', 'MP3')

3. Functional Dependencies and schema validation

To validate the Relational Schema obtained from the Conceptual Model, all functional dependencies are identified and the normalization of all relation schemas is accomplished.

Table R01 (user)
Keys: {id}, {email}
Functional Dependencies
FD0101 {id} → {email, name, obs, password, img, is_admin}
FD0102 {email}→ {id, name, obs, password, img, is_admin}
NORMAL FORM BCNF
Table R02 (author)
Keys: {id}
Functional Dependencies
FD0201 {id} → {name, img}
NORMAL FORM BCNF
Table R03 (collection)
Keys: {id}
Functional Dependencies
FD0301 {id} → {name}
NORMAL FORM BCNF
Table R04 (work)
Keys: {id}
Functional Dependencies
FD0401 {id} → {title, obs, img, year, id_user, id_collection}
NORMAL FORM BCNF
Table R05 (author_work)
Keys: {id_author, id_work}
Functional Dependencies
(none)
NORMAL FORM BCNF
Table R06 (nonbook)
Keys: {id_work}
Functional Dependencies
FD0601 {id_work} → {type}
NORMAL FORM BCNF
Table R07 (publisher)
Keys: {id}
Functional Dependencies
FD0701 {id} → {name}
NORMAL FORM BCNF
Table R08 (book)
Keys: {id_work},{isbn}
Functional Dependencies
FD0801 {id_work} → {edition, isbn, id_publisher}
FD0802 {isbn}→ {id_work, edition, id_publisher}
NORMAL FORM BCNF
Table R09 (location)
Keys: {id}
Functional Dependencies
FD0901 {id} → {name, address, gps}
NORMAL FORM BCNF
Table R10 (item)
Keys: {id}
Functional Dependencies
FD1001 {id} → {id_work, id_location, code, date}
NORMAL FORM BCNF
Table R11 (loan)
Keys: {id}
Functional Dependencies
FD1101 {id} → {id_item, id_user, start, end}
NORMAL FORM BCNF
Table R12 (review)
Keys: {id_user, id_work}
Functional Dependencies
FD1201 {id_user, id_work} → {date, comment, rating}
NORMAL FORM BCNF
Table R13 (wish_list)
Keys: {id_user, id_work}
Functional Dependencies
(none)
NORMAL FORM BCNF

Because all relations are in the Boyce–Codd Normal Form (BCNF), the relational schema is also in the BCNF and therefore there is no need to be refined it using normalisation.

4. SQL Code

database.sql
-- Types
 
CREATE TYPE media AS ENUM ('CD', 'DVD', 'VHS', 'Slides', 'Photos', 'MP3');
 
-- Tables
 
 
CREATE TABLE "user" (
    id SERIAL PRIMARY KEY,
    email text NOT NULL CONSTRAINT user_email_uk UNIQUE,
    name text NOT NULL,
    obs text,
    password text NOT NULL,
    img text,
    is_admin BOOLEAN NOT NULL
);
 
CREATE TABLE publisher (
    id SERIAL PRIMARY KEY,
    name text NOT NULL
);
 
CREATE TABLE location (
    id SERIAL PRIMARY KEY,
    name text NOT NULL,
    address text NOT NULL,
    gps text
);
 
CREATE TABLE author (
    id SERIAL PRIMARY KEY,
    name text NOT NULL,
    img text
);
 
CREATE TABLE collection (
    id SERIAL PRIMARY KEY,
    name text NOT NULL
);
 
CREATE TABLE "work" (
    id SERIAL PRIMARY KEY,
    title text NOT NULL,
    obs text,
    img text,
    "year" INTEGER,
    id_user INTEGER REFERENCES "user" (id) ON UPDATE CASCADE,
    id_collection INTEGER REFERENCES collection (id) ON UPDATE CASCADE,
    CONSTRAINT year_positive_ck CHECK (("year" > 0))
);
 
CREATE TABLE author_work (
    id_author INTEGER NOT NULL REFERENCES author (id) ON UPDATE CASCADE,
    id_work INTEGER NOT NULL REFERENCES "work" (id) ON UPDATE CASCADE,
    PRIMARY KEY (id_author, id_work)
);
 
CREATE TABLE book (
    id_work INTEGER PRIMARY KEY REFERENCES "work" (id) ON UPDATE CASCADE,
    edition text,
    isbn BIGINT NOT NULL CONSTRAINT book_isbn_uk UNIQUE,
    id_publisher INTEGER REFERENCES publisher (id) ON UPDATE CASCADE
);
 
CREATE TABLE nonbook (
    id_work INTEGER PRIMARY KEY REFERENCES "work" (id) ON UPDATE CASCADE ON DELETE CASCADE,
    TYPE media NOT NULL
);
 
CREATE TABLE item (
    id SERIAL PRIMARY KEY,
    id_work INTEGER NOT NULL REFERENCES "work" (id) ON UPDATE CASCADE,
    id_location INTEGER NOT NULL REFERENCES location (id) ON UPDATE CASCADE,
    code INTEGER NOT NULL,
    "date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL
);
 
CREATE TABLE loan (
    id SERIAL PRIMARY KEY,
    id_item INTEGER NOT NULL REFERENCES item (id) ON UPDATE CASCADE,
    id_user INTEGER NOT NULL REFERENCES "user" (id) ON UPDATE CASCADE,
    start_t TIMESTAMP WITH TIME zone NOT NULL,
    end_t TIMESTAMP WITH TIME zone NOT NULL,
    CONSTRAINT date_ck CHECK (end_t > start_t)
);
 
CREATE TABLE review (
    id_work INTEGER NOT NULL REFERENCES "work" (id) ON UPDATE CASCADE,
    id_user INTEGER NOT NULL REFERENCES "user" (id) ON UPDATE CASCADE,
    "date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
    comment text NOT NULL,
    rating INTEGER NOT NULL CONSTRAINT rating_ck CHECK (((rating > 0) OR (rating <= 5))),
    PRIMARY KEY (id_work, id_user)
);
 
CREATE TABLE wish_list (
    id_work INTEGER NOT NULL REFERENCES "work" (id) ON UPDATE CASCADE,
    id_user INTEGER NOT NULL REFERENCES "user" (id) ON UPDATE CASCADE,
    PRIMARY KEY (id_work, id_user)
);

LBAW

[MediaLibrary]

teach/lbaw/medialib/a5.txt · Last modified: 22/03/2019 20:22 by Correia Lopes