Database Systems
Lecture – 2: RELATIONAL MODEL
Ali Zidane ElQutaany
Database System Lifecycle
• Requirement Analysis
• DB Design logically
• Physical DB and upload data
• Creating application program
Three-Schema Architecture
External External External
View #1 View #2
... View #n
Mapping
Conceptual Schema
Mapping
Internal Schema
Stored database
Three-Schema Architecture
• Defines DBMS schemas at three levels:
– Internal schema is used to describe physical storage
structures and access paths (e.g indexes).
• Typically uses a physical data model.
– Conceptual schema is used to describe the structure
and constraints for the whole database for a community
of users.
– External schemas is used to describe the various user
views.
Data Models
Data Models
• A collection of tools for describing:
– Data
– Data Relationships
– Data Semantics
– Data Constraints
• Models:
– Relational Model
– Object-oriented model
– Semi-structured data models
– Older models: network model and hierarchical
model
History of Relational Model
• Introduced by Ted Codd in 1970 in a classic
paper.
• Ted Codd was an IBM Researcher.
• Many database concepts & products based on
this model.
Relational Model
Relations
• A relational database is a set of relations.
• Relations are basically tables of data.
• Each row represents a record in the relation.
• Each relation has a unique name in the database.
• Each row in the table specifies a relationship between the values in
that row.
• Example :
The account ID “A-307”, branch name “Seattle” and balance “275” are
all related to each other.
Relation In RDB
Table name
Attribute
Supplier
Table
Heading S# SNAME STATUS CITY
S1 Smith 20 London
Jones 10 Paris
S2
Black 30 Paris
S3
Clark 20 London
Tuple S4
(Row) Adams 30 Athens
S5
Data value Relation
Cardinality
Relation Degree
Relation Example
11
Relations and Attributes
• Each relation has some number of attributes.
• Sometimes called “columns”.
• Each attribute has a domain specifies the set of valid values for the
attribute.
• The account relation:
• 3 attributes
• Domain of balance is the set of nonnegative integers
• Domain of branch_name is the set of all valid branch names in the bank
12
DOMAINS
• A domain D is a set (pool) of values, from which one or
more attributes takes their values.
• Example
CITY = {London, Paris, Doha, Cairo, Athens, Rome, Dubai, Madrid}
CITY is a pool of cities from which the attributes [Link], [Link] take
their own values.
DATE = (DAY, MONTH, YEAR)
Where:
DAY = {1..31}, MONTH = {1..12}, YEAR = {1990..2100}
CITY is a simple domain, but DATE is a composite Domain
13
Tuples and Attributes
• Each row is called a tuple
– A fixed-size, ordered set of name-value pairs
• Each attribute in the tuple has a unique name
14
Tuples and Relations
• A relation is a set of tuples.
• Each tuple appears exactly once.
• The order of tuples in a relation is not relevant.
15
Schema VS Instance
• The name of the relation and the set of attributes
is called the schema.
• The current values contained in the relation
represent an instance.
16
Relation Schemas
• Every relation has a schema.
• A relation schema includes:
– An ordered set of attributes
– The domain of each attribute
• The relation schema of account is:
Account_schema = (acct_id, branch_name, balance)
17
COMPANY Database Schema
18
Database Schema and Instance
• The Schema (or description) of a Relation:
– Denoted by R(A1, A2, .....An)
– R is the name of the relation
– The attributes of the relation are A1, A2, ..., An
• Example:
CUSTOMER (Cust-id, Cust-name, Address, Phone#)
• Each attribute has a domain or a set of valid values.
– For example, the domain of Cust-id is 6 digit numbers.
• Instance of R is a set of tuples satisfy the schema of R
• Note
– Database schema stable over long period of time
– Database instance change constantly as data inserted or deleted
19
Types of DB Constraints
1. Domain constraints
2. Key constraints
3. Integrity constraints
– Entity Integrity Constraint
– Referential Integrity Constraint
4. Semantic Integrity Constraints
20
1- Domains Constraints
• The relational model constrains attribute
domains to be atomic
– Values are indivisible units
• Attribute domains may also include the null
value
– null = the value is unknown or unspecified
– null can often complicate things. Generally
considered good practice to avoid wherever
reasonable to do so.
21
Domain Constraints
Example: “Mobile number” are the set of 11
digits phone numbers valid in Egypt .
Example: Departments of a students are the set of
faculty department names
22
2- Key Constraints
• Value of a key uniquely identifies a tuple in a
relation
• A key is number of attributes which we cannot
remove any attributes and still be able to uniquely
identify tuples in a relation
• A relational schema may have more than one key
– Each key called a candidate key
– One designated as the primary key
23
Examples from Premier Database –
Primary Key
CustomerNum uniquely identifies the Customer table
and is the primary key of this table.
24
Examples from Premier Database –
Primary Key
OrderNum and PartNum make up the primary key Of the OrderLine table. This is
what is known as a Composite Primary key, that is, primary key that is made up of
more than one field.
25
Other keys
Value of a key uniquely identifies a tuple in a
relation
• Candidate key: any subset of attributes that can
be a primary key.
• The primary key is stable and minimum.
• A super key K is subset of attributes of R
such that:
– No 2 tuples have same values for K
26
Foreign Keys
• A foreign key in R is a set of attributes FK in R
such that FK is a primary key of some other
relation R’
• A foreign key is used to specify a referential
integrity constraint
27
Example
Employee
Enum Ename phone projectnum
Project Schema
Pnum Pname Location
28
Example
Employee
Enum Ename phone
phone Projectnum
123 Ahmed 01110025878 111
124 Ali 01225929785
127 Ola 0102457896 111
Instances
Project
Pnum Pname Location
111 ABC Giza
112 EFG Cairo
29
Example of composite FK
Employee
Enum Ename
Enum phone
Ename Pname Location
123 Ahmed 01110025878 ABC Giza
124 Ali 01225929785
127 Ola 0102457896 EFG Cairo
Project
Pname Location
ABC Giza
EFG Cairo
30
Key Constraints
• The two important keys are
– Primary key
• Unique
• Not null
• Stable and minimum # of attributes
– Foreign key
• Matches a primary key
• Can accept Null
31
Slide 32
Key Constraints
• Example: Consider the CAR relation schema:
– CAR(State, Reg#, SerialNo, Make, Model, Year)
– CAR has two candidate keys:
• Key1 = {State, Reg#}
• Key2 = {SerialNo}
– {SerialNo, Make} is a super key.
• In general:
– Any key is a super key (but not vice versa)
– Any set of attributes that includes a key is a super key
– A minimal super key is also a primary key
33
DB02: Relational Model Slide 34
Company Relational Schema
Diagram
• The primary key attribute (or attributes) will be underlined
• A foreign key (referential integrity) constraints is displayed as a directed arc (arrow)
from the foreign key attributes to the referenced table
35