SCOTT.T_BORROWING

General Info

Caption Borrowing

Attributes

Key Attribute Name Domain Data Type Not Null Unique Check Default
PFK exemplar_id Integer YES NO NO
FK customer_id Integer YES NO NO
start_date Date NO NO NO sysdate
end_date Date NO NO NO
total_price SCOTT.Price_Type NO NO NO
VAT VAT Number(4,2) NO NO NO 19

Relationships

Relationship Name Relationship Type Parent Entity Child Entity Card.
makes Non-identifying SCOTT.T_CUSTOMER SCOTT.T_BORROWING 1:5
is_related_to Identifying SCOTT.T_EXEMPLAR SCOTT.T_BORROWING 1:1

Keys

Key Type Constraint Name Attributes
PK pk_T_BORROWING exemplar_id

Check Constraints

Check Constraint Name check_end_after_start
Check
(end_date>start_date) or (end_date is null)

Triggers

Trigger Name
SCOTT.tri_BORROWING
SCOTT.tru_BORROWING

Comments

Borrowed items

 

SCOTT.T_CUSTOMER

General Info

Caption Customer

Attributes

Key Attribute Name Domain Data Type Not Null Unique Check Default
PK customer_id Integer YES NO NO
name Varchar2(20 CHAR) NO NO NO
address SCOTT.Cust_Address_Type NO NO NO

Relationships

Relationship Name Relationship Type Parent Entity Child Entity Card.
makes Non-identifying SCOTT.T_CUSTOMER SCOTT.T_BORROWING 1:5
places Identifying SCOTT.T_CUSTOMER SCOTT.T_ORDER_RECORD 1:N

Keys

Key Type Constraint Name Attributes
PK pk_T_CUSTOMER customer_id

Indexes

Index Name Attributes Unique Descending Clustered
i_name name NO

 

SCOTT.T_CUSTOMER_RATING

General Info

Caption Customer Rating

Attributes

Key Attribute Name Domain Data Type Not Null Unique Check Default
PFK title Varchar2(20 CHAR) YES NO NO
PFK director Varchar2(20 CHAR) YES NO NO
rating Integer NO NO YES 3

Relationships

Relationship Name Relationship Type Parent Entity Child Entity Card.
is_rated Identifying SCOTT.T_FILM SCOTT.T_CUSTOMER_RATING 1:N

Keys

Key Type Constraint Name Attributes
PK pk_T_CUSTOMER_RATING title, director

Comments

Movie ratings (by customers)

 

SCOTT.T_EXEMPLAR

General Info

Caption Exemplar

Attributes

Key Attribute Name Domain Data Type Not Null Unique Check Default
PK exemplar_id Integer YES NO NO
FK film_id Integer YES NO NO
FK medium_id Integer YES NO NO
price_per_day Integer NO NO NO

Relationships

Relationship Name Relationship Type Parent Entity Child Entity Card.
is_related_to Identifying SCOTT.T_EXEMPLAR SCOTT.T_BORROWING 1:1
is_available_on Non-identifying SCOTT.T_MEDIUM SCOTT.T_EXEMPLAR 1:N
has Non-identifying SCOTT.T_FILM SCOTT.T_EXEMPLAR 1:N

Keys

Key Type Constraint Name Attributes
PK pk_T_EXEMPLAR exemplar_id
AK ak_exemplar_ppd exemplar_id, price_per_day

 

SCOTT.T_FILM

General Info

Caption Film

Attributes

Key Attribute Name Domain Data Type Not Null Unique Check Default
PK film_id Integer YES NO NO
title Varchar2(20 CHAR) YES NO NO
director Varchar2(20 CHAR) YES NO NO
production_company Varchar2(20 CHAR) NO NO NO
FK genre_id Integer YES NO NO
min_age Integer NO NO NO
FK film_ID_episodes Integer NO NO NO

Relationships

Relationship Name Relationship Type Parent Entity Child Entity Card.
is_required_by Identifying SCOTT.T_FILM SCOTT.T_ORDER_RECORD 1:N
is_of Non-identifying SCOTT.T_GENRE SCOTT.T_FILM 1:N
has Non-identifying SCOTT.T_FILM SCOTT.T_EXEMPLAR 1:N
is_rated Identifying SCOTT.T_FILM SCOTT.T_CUSTOMER_RATING 1:N
has_more_episodes Non-identifying SCOTT.T_FILM SCOTT.T_FILM 1:N

Keys

Key Type Constraint Name Attributes
PK pk_T_FILM film_id
AK ak_title_director title, director

 

SCOTT.T_GENRE

General Info

Caption Genre

Attributes

Key Attribute Name Domain Data Type Not Null Unique Check Default
PK genre_id Integer YES NO NO
name Varchar2(20 CHAR) NO NO NO

Relationships

Relationship Name Relationship Type Parent Entity Child Entity Card.
is_of Non-identifying SCOTT.T_GENRE SCOTT.T_FILM 1:N

Keys

Key Type Constraint Name Attributes
PK pk_T_GENRE genre_id

 

SCOTT.T_MEDIUM

General Info

Caption Medium

Attributes

Key Attribute Name Domain Data Type Not Null Unique Check Default
PK medium_id Integer YES NO NO
medium_type Varchar2(20 BYTE) NO NO NO

Relationships

Relationship Name Relationship Type Parent Entity Child Entity Card.
is_available_on Non-identifying SCOTT.T_MEDIUM SCOTT.T_EXEMPLAR 1:N

Keys

Key Type Constraint Name Attributes
PK pk_T_MEDIUM medium_id

 

SCOTT.T_ORDER_RECORD

General Info

Caption Order Record

Attributes

Key Attribute Name Domain Data Type Not Null Unique Check Default
PFK customer_id Integer YES NO NO
PFK film_id Integer YES NO NO
order_date Date NO NO NO

Relationships

Relationship Name Relationship Type Parent Entity Child Entity Card.
places Identifying SCOTT.T_CUSTOMER SCOTT.T_ORDER_RECORD 1:N
is_required_by Identifying SCOTT.T_FILM SCOTT.T_ORDER_RECORD 1:N

Keys

Key Type Constraint Name Attributes
PK pk_T_ORDER_RECORD customer_id, film_id

Comments

All records are stored in list of records. It will be possible to book a movie.