Course: Introduction to Database Systems

Course Code: IS211

DBMS (Continued) and Relational

Model

1
Blackboard

Enroll_Access_
Course_ID
Code

835223 [Link].IS211

2
DBMS Examples
• Oracle
• MySQL
• MS SQL Server

3
DBMS Interfaces
• Menu-based
• Graphical user interface
• Query language

4
Classification of DBMS

Based on:
• Data model
➢ Relational, network, hierarchical, object-oriented …etc.

• Number of users
➢ Single user or multi-user

• Database distribution
➢ Centralized vs. distributed

5
Three-Schema Architecture

External External External

View #1 View #2
... View #n

mapping
Conceptual Schema
mapping
Internal Schema

Stored database 6
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.

7
Data Model

8
Data Models
• A database model is a type of model that determines the logical structure of a
database.

• A collection of tools for describing:

• data
• data relationships
• data semantics
• data constraints

• Models:
• Relational Model
• Object-oriented model
• Older models: network model and hierarchical model
9
Relational Model

10
History of Relational Database Model
• Introduced by Ted Codd in 1970 in a classic paper.
• Ted Codd was an IBM Research Fellow.
• Laid the foundation for database theory.
• Many database concepts & products are based on this model.
• Most common used database model.

11
Relational Model
• Based on “2-dimensional table” with rows and columns called a relation.
• Each row (tuple), is a collection of related facts.
• Each column represents an attribute.
• The degree of the relation is the number of attributes in a relation.
• A relational database contains one or more relations, or tables.

12
A Relation in Relational Database (RDB)

13
Example: Student Relation

STUDENT (Name, Ssn, Home_phone, Address, Office_phone,

Age, Gpa)

14
Characteristics of Relations

• Tuples have no particular order

• Ordering of attributes is not important
• All values belonging to a particular attribute are from the same domain
• Attributes are atomic
• Attributes may have a null value: null is not a zero

15
Schema VS Instance
• The name of the relation and the set of attributes is called the schema.

• A schema is the way the database designer describes the structure of the
database. It contains the definition of the data itself with the identification of
the relationship between such data definition.

• The current values contained in the relation represent an instance.

16
Relations
• A relational database is a set of relations.
• Each relation has a unique name in the database.
• Relations are basically tables of data.
• Each row is called a tuple.
• Each row represents a record in the relation.
• Each attribute/column has a unique name.
• 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. 17
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, Dobai, 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. 18

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)

19
COMPANY Database Schema

20
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.
• Notes
• Database schema stable over long period of time
• Database instance change constantly as data inserted or deleted
21
Types of DB Constraints
1. Domain constraints
2. Key constraints
3. Integrity constraints
• Entity Integrity Constraint
• Referential Integrity Constraint
• Semantic Integrity Constraints

22
Domains Constraints
• The value of each attribute, A, must be an atomic value from the domain of A.
• Values are indivisible units.

23
Domain Constraints: Examples
Example “Mobile number” are the set of 11 digit phone numbers valid in Egypt .

Example “dept” of a students are the set of faculty department names.

24
 Key Constraints
• Value of a key uniquely identifies a tuple in a relation.

• Super key (K): is a subset of attributes of R that can identify a tuple. It might
contain more than one attribute.
• No 2 tuples have same values for K.
• A key is a minimal superkey; a super key from which we can not 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

25
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 superkey.
• In general:
• Any key is a superkey (but not vice versa)
• Any set of attributes that includes a key is a superkey
• A minimal superkey is also a primary key

26
Examples from Premier Database – Primary Key

CustomerNum uniquely identifies the Customer table

and is the primary key of this table.
27
Examples from Premier Database – Primary Key

OrderNum and PartNum makes 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.
28
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.

29
Example
Employee
Enum Ename phone projectnum

Project Schema
Pnum Pname Location

30
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

31
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

32
33
DB02: Relational Model 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
Entity Integrity Constraint
- A primary key value cannot be null.

36
Semantic Integrity Constraints

• Constraints on data values.

• Examples:
➢The salary of an employee must not exceed that of her supervisor.
➢A person’s date of birth must be before the current date.

37
Any Questions?

38