Chapter – Two

Database System Concepts and

Architecture

1
What is Data model ?

2
 Data Model

Data Model: A set of concepts to describe the structure of a

database, the operations for manipulating these structures and
certain constraints that the database should obey.

• a collection of concepts that can be used to describe the structure of

a database

•Structure of a database: means the data types, relationships, and

constraints that apply to the data.

• What do we mean by the Structure of a database?

3
 Data Model….

• Operations on the data model may include basic operations

and user-defined operations.

• Basic operations to insert, delete, modify, or retrieve any kind of object

are often included in the basic data model operations

• User-defined operations (e.g. compute_student_gpa, update_inventory)

• What are the two operation on the data model of a database?

4
 Categories of data models
(Stages of Data model )

1. Conceptual (high-level, semantic) data models:

Provide concepts that are close to the way many users perceive data.
The 3 basic tenants of Conceptual Data Model are
Entity: A real-world thing
Attribute: Characteristics or properties of an entity
Relationship: Dependency or association between two entities
It describes what the system contains

5
 Categories of data models….

 Physical (low-level, internal) : Provide concepts that describe details

of how data is stored in the computer.

6
 Categories of data models….
• Implementation (representational) data models :
• Which provide concepts that may be easily understood by end
users but that are not too far removed from the way data is
organized in computer storage.
• Representational data models hide many details of data storage
on disk but can be implemented on a computer system directly.

7
 Schema vs Instance
Database Schema: The over all description of a database. /intention
Database schema = Variable declaration.
Schema is specified during database design and it is not expected to
change frequently. (b/c developed based on the user requirement )

Schema Construct: Each object in the schema, e.g., STUDENT,

COURSE , SECTION etc..

Database Instance: The actual data stored in a database at a

particular moment in time. Also called database state/ a snapshot of
a DB at specific moment

Schema is specified during database design and it is not expected to

change frequently. (b/c developed based on the user requirement )

We can not say the content of the DB since the DB changing

regularly by insert, update or delete operation 8
Examples of Database Schema

9
 Database Schema Vs. Database State

 Initial Database State: Refers to the database

when it is loaded
– Valid State: A state that satisfies the structure and
constraints of the database.
 The database state changes every time the database
is updated.

10
 Three-Schema Architecture
Defines DBMS schemas at three levels(in design perspective ):

•It describes how data in the database viewed by user

The bank ATM example

1) Insert card into machine
2) provide card details pin
etc.
3) Specify amount to be
withdrawn
4) Machine does processing
5) Transaction completed,
database updated

11
 Three-Schema Architecture
External schemas (external level) at the external level to describe
the various user views( includes Multiple of user)

provide a separate view for different groups of user

 Each view represent a part of database
It allows user to access data in a customize manner based on in
there requirement ,

12
Three-Schema Architecture……
2. Conceptual schema / the conceptual level
• It describes the structure of the database.
• It hides the details of the physical storage structure.
• It describes entities, data types, relationships, user operations and
constraints.
3. Internal schema/Internal level.
• Describes the physical storage structures of the database.
• It uses physical data model.
• It describes the complete details of data and access paths for the
database.
 E.g. Age holds 8bits ,
• Managed by Operating system under the direction of DBA

13
 Data Independence

Data independence is a capacity of DBMS to change the

schema at one level of a database system without having to
change the schema at the next higher level.

1. Logical Data Independence: The capacity to change the

conceptual schema without having to change the external
schemas and their application programs.

2. Physical Data Independence: The capacity to change the

internal schema without having to change the conceptual
schema.

14
 DBMS Languages

1. DDL(Data Definition Language ), is used by the DBA and

Database designers to specify the database schema .
Create Database instance ,Alter , drop , rename and truncate

2. DML (Data manipulation language), is used accessing and

manipulate the database data .

Include select, insert , update and Delete

15
 Classification of DBMSs
• Based on the data model used
– Traditional: Network, Hierarchical.
– Emerging: Object-oriented, Object-relational.
• Other classifications
– Single-user (typically used with personal
computers) vs. multi-user (most DBMSs).
– Centralized (uses a single computer with one
database) vs. distributed (uses multiple
computers, multiple databases)
 Relational Data Model
• The relational model represents the database as a collection of
relations.

• In the formal relational model terminology:

• Collection of tables.
• Tables represent both data and relationships.
• Multiple columns with unique names.
• A row is called a tuple.
• A column header is called an attribute, and.
• The table is called a relation.
• The data type describing the types of values that can appear in each
column is represented by a domain of possible values. 17
 Example

• Each row in the STUDENT table represents facts about a

particular student entity.
• The column names: Name, Ssn, home_phone, and Address
specify how to interpret the data values in each row, based on
the column each value is in.
• All values in a column are of the same data type.
18
 Relational Data Model……
• A Relation may be defined in multiple ways.
• The Schema of a Relation: R (A1, A2, .....An)
Relation schema R is defined over attributes A1,
A2, .....An
For Example -
CUSTOMER (Cust-id, Cust-name, Address, Phone#)

Here, CUSTOMER is a relation defined over the four

attributes Cust-id, Cust-name, Address, Phone#, each of
which has a domain or a set of valid values.
For example, the domain of Cust-id is 6 digit numbers.
19
 Relational Data Model……

• Each row in the CUSTOMER table may be referred

to as a tuple in the table and would consist of
four values.
• <632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894-2000">
is a tuple belonging to the CUSTOMER relation.
• A relation may be regarded as a set of tuples
(rows).
• Columns in a table are also called attributes of
the relation.
20
 Relational Data Model……

An n-tuple ---> ordered list of n values, so ordering of values in

a tuple is important.
21
 Characteristics of Relations….
Ordering of values with a tuples and alternative definition of
relation:
• The ordering of values within tuple is important.
 An alternative definition of a relation can be given, making the
ordering of values in a tuple unnecessary.
 A tuple --> set of(<attribute>),<values> pair, then ordering of
attributes is not important.

22
Values and NULLs in the Tuples:
 Each value in a tuple is an atomic value.

 Composite and multi valued attributes are not allowed.

 This model is sometimes called the flat relational model.

 Multi valued attributes must be represented by separate

relations, this called first normal form.
 Composite attributes are represented only by their simple
component attributes in the basic relational model.
 NULL values are used to represent the values of attributes
that may be unknown or may not applicable for the tuple .23
Values and NULLs in the Tuples:

24
Relational Data Model……

25
Database Key

26
Database Key

27
 Relational Model Constraints
• There are generally many restrictions or constraints on the actual values in
a database state.
• These constraints are derived from the rules in the mini-world that the
database represents.
• Constraints on databases can generally be divided into three main
categories:
1. Inherit model-based constraints or implicit constraints:
• Constraints that are inherent in the data model. E.g. no duplicate tuple in
the relation
2. Schema-based constraints or explicit constraints:
• Constraints that can be directly expressed in schemas of the data model,
typically by specifying them in the DDL. E.g. Age of emp. > 20,
3. Application-based or semantic constraints or business rules:
• Constraints that cannot be directly expressed in the schemas of the data
model, and hence must be expressed and enforced by the application
programs.
28
 Relational Integrity Constraints

 Constraints are conditions that must hold on all valid

relation instances.
 There are four main types of constraints:

1. Domain constraints
2. Key constraints
3. Entity integrity constraints
4. Referential integrity constraints

29
 Domain constraints
• Every value in a tuple must be from the domain of its attribute (or
it could be null, if allowed for that attribute
Example
• The datat ype and the length
• The NULL value acceptance
• The allowable values, through techniques like constraints or rules
• The default value

30
 Key Constraints
Super key of R: A set of attributes SK of R such that no two tuples
in any valid relation instance r(R) will have the same value for
SK. That is, for any distinct tuples t1 and t2 in r(R), t1[SK] 
t2[SK].
- A super key specifies that no two tuples can have the same
values (identical value)
• If a relation has several candidate keys, one is chosen
arbitrarily to be the primary key. The primary key attributes
are underlined.
 The CAR relation, with two candidate keys: License_Number
and Engine_Serial_Number.
 Sk=(License_Number) & (Engine_Serial_Number)
• (License_Number, Model),( mark,Engine_Serial_Number),
• (License_Number, year)
31
• The CAR relation, with two candidate keys: License_Number and
Engine_Serial_Number.

 Null value constraints:

This constraint specifies whether NULL values are or are not
permitted on attribute..

32
 Entity integrity constraint

• The entity integrity constraint states that primary key

value can't be null.
• This is because the primary key value is used to identify
individual rows in relation and if the primary key has a
null value, then we can't identify those rows.

33
 Referential Integrity
• A constraint involving two relations (the previous constraints
involve a single relation).
• Used to specify a relationship among tuples in two relations: the
referencing relation and the referenced relation.
• Tuples in the referencing relation R1 have attributes FK (called
foreign key attributes) that reference the primary key attributes PK
of the referenced relation R2. A tuple t1 in R1 is said to reference a
tuple t2 in R2 if t1[FK] = t2[PK].
• A referential integrity constraint can be displayed in a relational
database schema as a directed arc from [Link] to R2.

34
Referential Integrity…..

35
• Referential integrity is depends on foreign key(FK).
• Let us assume two relations R1 and R2, these two relations are relate
each other.
• A set of attributes FK in relation schema R1 is a foreign key of R1 that
references relation R2 if it satisfies the following rules:
1. The attributes in FK have the same domain(s) as the primary key
attributes PK of R2, the attributes FK are said to reference or refer to
the relation R2.
2. A value of FK in a tuple t1 of the current state r1(R1) either occurs as a
value of PK for some tuple t2 in the current state r2(R2) or is NULL. In
the former case, we have t1[FK] = t2[PK],and we say that the tuple t1
references or refers to the tuple t2.
• R1 is called the referencing relation and R2 is the referenced relation.
• If these two conditions hold, a referential integrity constraint from R1 to
R2 is said to hold. In a database of many relations, there are usually many
36
Referential Integrity….

37
 SQL
Create table project (
Project_id int primary key, Create table customer (
Project_Name varchar(20), Customer_id int primary key,
Start_Date date ,`
Budget int , Customer_name varchar(50)
Customer_id int );

Foreign key(Customer_id)references
customer(Customer_id )
);
Insert into project values (Db upgrade, 110, 19Nov 2015,48700,2)
Insert into project values (New email server, 1101 ,09 Jan 2016,411900,2)
Insert into project values (3D printers, 112, 06 Jul 2016,42500,1)
Insert into project values (Networkupgrade, 113, 24Nov 2015,43700,5)

Insert into customer values (1 , Dan)

Insert into customer values (2 , Priya)

38
 Chapter Summery
Employee/ Child table
EmpID Name Age phone DNo
1330 Daniel 29 65982345 2
1331 Mohamed 31 45239734 1

Department/ Main Table/Parent

DNo DName Location
1 IT Dato
2 CS Piassa

39
Insert operation

• Domain constraints can be violated if the given attribute value

doesn’t appear in the corresponding domain.
EmpID Name Age phone DNo
1330 Daniel 29 65982345 2
1331 Mohamed 31 45239734 1
1332 Abebe 28 98765432 A

40
 Insert operation
insert in to employee values ( 1330, ‘Harry', 34, 652562514, 1) ;
insert in to employee values ( Null, ‘Melate', 25, 752562514, 2)

Employee
EmpID Name Age phone DNo
1330 Daniel 29 65982345 2
1331 Mohamed 31 45239734 1
1330 Harry 34 47890987 1
Null Melate 25 75256514 2

Violates Entity Integrity Constraint

Violate Key constraint 41

Insert operation
insert in to employee values ( 1332, ‘Tigist', 34, 852562514, 5) ;

Employee
EmpID Name Age phone DNo
1330 Daniel 29 65982345 2
1331 Mohamed 31 45239734 1
1332 Tigist 34 852562514 5

Department
DNo DName Location
1 IT Dato
2 CS Piassa

Violates Referential Integrity constrain

42
 Update Operation
The Delete Operations

Employee
EmpID Name Age phone DNo
1330 Daniel 29 65982345 2
1331 Mohamed 31 45239734 1
Project
PNo ProjectName DNo
1 Application Development 2
2 Network infrastructure 1
Works_ON
EmpID PNo Hours
1331 1 40
1331 1 56
43
1330 2 45
 Update Operation
• Ex: (I) Delete the Employee tuple with Emld = ‘1331’
(Violates Referential Integrity constraint )
• (2) Delete the Works_On tuple withID=1331 and PN0=1
(acceptable )
• (3)Update the Dno of the Employee tuple with EmpID-1330 to 1
(acceptable)
• (4) Update the Dno of the Employee tuple with EmpID=1330 to 7
( violates referential integrity)
(5) Update the EmpID of the Employee tuple with EmID-1330 to 1331
(violate key constraint and referential integrity constraint)

44
End of Chapter 2!
NEXT CHAPTER 3

45