REL ATIONAL

DATABASE MODEL
LECTURE 3
DBMS
• Database – a collection of data that is logically coherent.
• DBMS – Database Management System
– defines, creates, and maintains a database.
– Allows users controlled access to data in the database.
– A combination of 5 components:
• Hardware
• Software
• Data
• Users
• Procedures
DBMS COMPONENTS

• Hardware –
the physical computer system that allows physical access to data.
• Software –
the actual program that allows users to access, maintain, and update physical data.
• Data – stored physically on the storage devices
• Users –
– End users - Normal user and DBA (Database Administrator)
– Application programs
• Procedures –
a set of rules that should be clearly defined and followed by the users.
DATABASE ARCHITECTURE
DATABASE ARCHITECTURE

• Internal level –
– Determines where data are actually stored on the storage device.
– Low-level access method
• Conceptual level –
– Defines the logical view of the data
– The main functions of DBMS are in this level.
• External level –
– Interacts directly with the user.
– Change the data coming from the conceptual level to a format and view that
are familiar to the users.
INTERNAL/PHYSICAL LEVEL
• Physical schema describes details of how data is stored: tracks,
cylinders, indices etc.
• Early applications worked at this level - explicitly dealt with details.
• Problems:
Routines hardcoded to deal with physical representation.
Changes to data structure difficult to make.
Application code becomes complex since it must deal with
details.
Rapid implementation of new features impossible.
CONCEPTUAL LEVEL
• Hides details.
– In the relational model, the conceptual schema presents data
as a set of tables.
• DBMS maps from conceptual to physical schema automatically.
• Physical schema can be changed without changing application:
– DBMS must change mapping from conceptual to physical.
• Referred to as physical data independence.
EXTERNAL LEVEL
• In the relational model, the external schema also presents data as a
set of relations.
• An external schema specifies a view of the data in terms of the
conceptual level. It is tailored to the needs of a particular category of
users.
– Portions of stored data should not be seen by some users.
• Students should not see faculty salaries.
• Faculty should not see billing data.
– Information that can be derived from stored data might be viewed
as if it were stored.
• GPA not stored, calculated when needed.
DATA MODEL
• Schema: description of data at some level (e.g., tables, attributes,
constraints, domains)
• Model: tools and language for describing:
– Conceptual and external schema
• Data definition language (DDL)
– Integrity constraints, domains (DDL)
– Operations on data
• Data manipulation language (DML)
– Directives that influence the physical schema (affects
performance, not semantics)
• Storage definition language (SDL)
DATABASE MODELS
• A database model
– defines the logical design of data.
– Describes the relationships between different parts of data.
• models
– Hierarchical model
– Network model
– Relational model
HIERARCHICAL MODEL
• Data are organized as an upside down tree.
• Each entity has only one parent but can have several children.
NETWORK MODEL
• The entities are organized in a graph.
• Some entities can be accessed through several paths.
RELATIONAL MODEL
• Data are organized in two-dimensional tables called relations.
• The tables are related to each other.
• The most popular model.
RELATIONAL MODEL
• RDBMS (Relational Database Management System)
• External view:
The data are represented as a set of relations.
A relation is a two-dimensional table.
• This doesn’t mean that data are stored as tables;
the physical storage of the data is independent of
the way the data are logically organized.
RELATION
• Name – each relation in a relational database should have a name that is unique
among other relations.
• Attribute – each column in a relation.
– The degree of the relation – the total number of attributes for a relation.
• Tuple – each row in a relation.
– The cardinality of the relation – the total number of rows in a relation.
RELATION SCHEMA

• Relation name
• Attribute names and domains
• Integrity constraints - e.g.,:
– The values of a particular attribute in all tuples are unique
– The values of a particular attribute in all tuples are greater than
0
• Default values
16
INTEGRITY CONSTRAINTS

• Part of schema
• Restriction on state (or sequence of states) of database
• Enforced by DBMS
• Intra-relational - involve only one relation
– Part of relation schema
– e.g., all Ids are unique
• Inter-relational - involve several relations
– Part of relation schema or database schema

17
KINDS OF INTEGRITY CONSTRAINTS

• Static - limitation on state of database

– Syntactic (structural)
• e.g., all values in a column must be unique
– Semantic (involve meaning of attributes)
• e.g., cannot register for more than 18 credits
• Dynamic - limitation on sequence of database states (supported by
some DBMSs, but not in current SQL standard)
• e.g., cannot raise salary by more than 5%

18
KEY CONSTRAINT

• Values in a column (or columns) of a relation are unique: at most

one row in a relation instance can contain a particular value(s)
• Key - set of attributes satisfying key constraint
– e.g., Id in Student,
– e.g., (StudId, CrsCode, Semester) in Transcript

19
KEY CONSTRAINT (CON’T)

• Minimality - no subset of a key is a key

– (StudId, CrsCode) is not a key of Transcript
• Superkey - set of attributes containing key
– (Id, Name) is a superkey of Student
• Every relation has a key
• Relation can have several keys:
– primary key (Id in Student) – (cannot be null)
– candidate key ((Name, Address) in Student)
20
FOREIGN KEY CONSTRAINT
• Referential integrity => Item named in one relation must
correspond to tuple(s) in another that describes the item
– Transcript (CrsCode) references Course(CrsCode )
– Professor(DeptId) references Department(DeptId)
• a1 is a foreign key of R1 referring to a2 in R2 => if v is a value
of a1, there is a unique tuple of R2 in which a2 has value v
– This is a special case of referential integrity: a2 must be a candidate key
of R2 (CrsCode is a key of Course)
– If no row exists in R2 => violation of referential integrity
– Not all rows of R2 need to be referenced.: relationship is not symmetric
(some course might not be taught)
– Value of a foreign key might not be specified (DeptId column of some
professor might be null)
21
FOREIGN KEY CONSTRAINT
(EXAMPLE)
a2
a1 v3
v1 v5
v2 v1
v3 v6
v4 v2
-- v7
v3 v4
R1 R2
Foreign key
Candidate key

22
FOREIGN KEY (CON’T)
• Names of a1 and a2 need not be the same.
– With tables:
Teaching(CrsCode: COURSES, Sem: SEMESTERS, ProfId: INT)
Professor(Id: INT, Name: STRING, DeptId: DEPTS)
ProfId attribute of Teaching references Id attribute of Professor
• R1 and R2 need not be distinct.
– Employee(Id:INT, MgrId:INT, ….)
• Employee(MgrId) references Employee(Id)
– Every manager is also an employee and hence has a unique
row in Employee

23
FOREIGN KEY (CON’T)
• Foreign key might consist of several columns
– (CrsCode, Semester) of Transcript references (CrsCode, Sem) of Teaching
• R1(a1, …an) references R2(b1, …bn)
– There exists a 1 - 1 relationship between a1,…an and b1,…bn
– ai and bi have same domains (although not necessarily the same names)
– b1,…bn is a candidate key of R2

24
SEMANTIC CONSTRAINTS

• Domain, primary key, and foreign key are examples of structural

(syntactic) constraints
• Semantic constraints express rules of application:
– e.g., number of registered students  maximum enrollment

25
DATABASE
OPERATIONS
CREATING TABLES

CREATE TABLE TableName

(Column-definition [,Column-definition] ) ;

Column-definition is

ColumnName data-type [Constraint]

27
NAMING TABLES

• Start with Alphabetical

• Special characters as ( _, #, ) and numbers are allowed
• Not case sensitive (emp, Emp, EMP)
• Unique name within account
• Not an sql reserved word as Select, Update, ….
• Not more that 30 characters

28
EXAMPLE
• ًًExample for creating table PERSONS

CREATE TABLE PERSONS

( CODE NUMBER(3,0) NOT NULL,
NAME VARCHAR2(50) NOT NULL,
ADDRESS VARCHAR2(50)
);

Constraints Example
• NOT NULL
• PRIMAEY KEY
29
DEFAULT VALUES

CREATE TABLE INVOICE

( INV_NUM NUMBER(4,0) NOT NULL,
INV_TOTAL NUMBER(10,3) DEFAULT 0,
INV_DATE DATE DEFAULT SYSDATE
);

30
REMOVING TABLE
Syntax
DROP TABLE TableName ;

Example
DROP TABLE PERSONS;

• Note:
– The difference between Drop and DELETE
keywords

31
CHANGING TABLE DESIGN
ALTER TABLE

• Adding new fields

ALTER TABLE PERSONS
ADD (IDNUMBER VARCHAR2(10) );

• Modifying Field definition

ALTER TABLE PERSONS
MODIFY (IDNUMBER VARCHAR2(20));
32
OPERATIONS ON RELATIONS
• In a relational database, we can define several operations to
create new relations out of the existing ones.
• Basic operations:
– Insert
– Delete
– Update
– Select
– Project
– Join
– Union
– Intersection
– Difference
Insert operation

 An unary operation.
 Insert a new tuple into the relation.
INSERT
• insert into RELATION-NAME
values ( … , … , … )

 insert into COURSES

values ( “CIS52”,”TCP/IP”, 6 )
Delete operation

 An unary operation.
 Delete a tuple defined by a criterion from the relation.
DELETE
• delete from RELATION-NAME
where criteria

 delete from COURSES

where No=“CIS19”
Update operation

 An unary operation.
 Changes the value of some attributes of a tuple.
UPDATE
• update RELATION-NAME
set attribute1=value1, attribute2=value2, …

where criteria

 update COURSES
set Unit=6
where No=“CIS51”
Select operation

 An unary operation.
 It is applied to one single relation and creates another
relation.
 The tuples in the resulting relation are a subset of the tuples
in the original relation.
 Use some criteria to select
SELECT
• select *
from RELATION-NAME
where criteria

 select *
from COURSES
where Unit=5
Project operation
 An unary operation.
 It is applied to one single relation and creates another
relation.
 The attributes in the resulting relation are a subset of the
attributes in the original relation.
PROJECT
• select attribute-list
from RELATION-NAME

 select No, Unit

from COURSES
Join operation
 A binary operation.
 Combines two relations based on common attributes.
JOIN
• select attribute-list
from RELATION1,RELATION2
where criteria

 select No,Course-Name,Unit,Professor
from COURSES,TAUGHT-BY
where COURSES.No=TAUGHT-BY.No
Union operation
 A binary operation.
 Creates a new relation in which each tuple is either in the
first relation, in the second, or in both.
 The two relations must have the same attributes.
UNION
• select *
from RELATION1
union
select *
from RELATION2

 select *
from CIS15-Roster
union
select *
from CIS52-Roster
Intersection operation
 A binary operation.
 Creates a new relation in which each tuple is a member in
both relations.
 The two relations must have the same attributes.
INTERSECTION
• select *
from RELATION1
intersection
select *
from RELATION2

 select *
from CIS15-Roster
intersection
select *
from CIS52-Roster
Difference operation
 A binary operation.
 Creates a new relation in which each tuple is in the first
relation but not the second.
 The two relations must have the same attributes.
DIFFERENCE
• select *
from RELATION1
minus
select *
from RELATION2

 select *
from CIS15-Roster
minus
select *
from CIS52-Roster