Transcript data model - Computer Science

Database Modelling
Dr. Awad Khalil
Computer Science Department
AUC
CSCI 253 -- Database Modelling
1
Content

Database Structure

The Three-Layer Architecture

Data Models
 Classification
of DBMSs
CSCI 253 -- Database Modelling
2
Database Structure
database structure is the description and definition
of all basic structures such as simple conceptual files,
datatypes, relationships, and constraints that should hold
on the data.
A
 In
any data model it is important to distinguish between
the description of the database (Schema) and the
database itself (Instance).

CSCI 253 -- Database Modelling
3
Database Schema
 The
description of a
database is called the
database schema
(or the meta-data).

A database schema
is specified during
database design and
is not expected to
change frequently.
CSCI 253 -- Database Modelling
4
A Database Schema in SQL
CREATE TABLE PROJECT
CREATE TABLE EMPLOYEE
(PNAME
VARCHAR(15)
(FNAME
VARCHAR(15) NOT NULL,
NOT NULL,
MINIT
CHAR,
PNUMBER
INT
LNAME
VARCHAR(15) NOT NULL,
NOT NULL,
SSN
SSN_TYPE NOT NULL,
PLOCATION VARCHAR(15)
BDATE
DATE
DNUM
INT
ADDRESS VARCHAR(30),
NOT NULL,
SEX
CHAR,
PRIMARY KEY (PNUMBER),
SALARY
DECIMAL(10,2),
UNIQUE (PNAME)
SUPERSSN SSN_TYPE,
FOREIGN KEY (DNUM) REFERENCES
DEPARTMENT(DNUMBER));
DNO
INT
NOT NULL,
CREATE TABLE WORKS_ON
PRIMARY KEY (SSN),
(ESSN
SSN_TYPE NOT NULL,
FOREIGN KEY (SUPERSSN) REFERENCES
EMPLOYEE(SSN),
PNO
INT
NOT NULL,
FOREIGN KEY (DNO) REFERENCES
HOURS
DECIMAL(3,1) NOT NULL,
DEPARTMENT(DNUMBER));
PRIMARY KEY (ESSN, PNO),
CREATE TABLE DEPARTMENT
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN),
(DNAME
VARCHAR(15) NOT NULL,
FOREIGN KEY (PNO) REFERENCES PROJECT(PNUMBER));
DNUMBER INT,
NOT NULL,
CREATE TABLE DEPENDENT
MGRSSN
SSN_TYPE NOT NULL,
(ESSN
SSN_TYPE NOT NULL,
MGRSTARTDATE
DATE
DEPENDENT_NAME VARCHAR(15) NOT NULL,
PRIMARY KEY (DNUMBER),
SEX
CHAR,
UNIQUE (DNAME)
BDATE
DATE,
FOREIGN KEY (MGRSSN) REFERENCES
RELATIONSHIP
EMPLOYEE(SSN));
VARCHAR(8)
CREATE TABLE DEPT_LOCATIONS
PRIMARY KEY (ESSN, DEPENDENT_NAME),
(DNUMBER INT
NOT NULL,
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN));
DLOCATION VARCHAR(15) NOT NULL,
PRIMARY KEY (DNUMBER, DLOCATION),
FOREIGN KEY (DNUMBER) REFERENCES
CSCI 253 -- Database Modelling
DEPARTMENT(DNUMBER));
5
Database State (Instance)
 The
data in the
database
at
a
particular moment
of time is called the
database
state
(or instance).
CSCI 253 -- Database Modelling
6
The Three-Layer Architecture
End Users
External Level
External
View1
External
Viewn
external/conceptual
mapping
Conceptual Level
Conceptual Schema
conceptual/internal
mapping
Internal Level
Internal Schema
Stored Database
CSCI 253 -- Database Modelling
7
Data Models
A
data model is a set of concepts that can be used to
describe a database structure.
Data Models
High-level
(conceptual)
Data Models
Relational
Implementation
Data Models
Hierarchical
Network
CSCI 253 -- Database Modelling
Low-level
(physical)
Data Models
Objectoriented
8
DBMS Generations
CSCI 253 -- Database Modelling
9
Data Independence
 Data
independence is the capacity to change the
schema at one level of a database system without having
to change the schema at the next higher level. defined:

Logical data independence is the capacity to
change the conceptual schema without having to
change external schemas or Logical data application
programs.

Physical data independence is the capacity to
change the internal schema without having to change
the conceptual (or external) schemas.
CSCI 253 -- Database Modelling
10
Classification of DBMSs
 Classification
according to Data Model
DBMSs
Relational
Hierarchical
Network
CSCI 253 -- Database Modelling
Objectoriented
11
The Relational Data Model
relational data model represents the database as a
collection of tables, where each table can be stored as
separate file.
 The
 Examples






of commercial relational DBMSs:
DB2 from IBM
ORACLE from Oracle Corporation
Informix from Informix
SyBase from OpenSoft
SQL Server from Microsoft
MS-ACCESS from Microsoft
CSCI 253 -- Database Modelling
12
An Example of a Relational Database
CSCI 253 -- Database Modelling
13
The Network Data model
network
data
model
represents data
as a record
types.
An
example of a
network
model
is
known as the
CODASYL
DBTG model.
 The
CSCI 253 -- Database Modelling
14
The Hierarchical Data model

The
hierarchical
data
model
represents data
as hierarchical
tree structure.
Each
hierarchical
represents
a
number
of
related records.
CSCI 253 -- Database Modelling
15
The Object-Oriented Data model

The
object-oriented
data model defines a
database in terms of
objects, their properties,
and their operations.
Objects with the same
structure and behavior
belong to a class, and
classes are organized
into hierarchies or a
cyclic
graphs.
The
operations of each class
are specified in terms of
predefined procedures
called methods.
Experimental OO prototypes
The ORION system developed at MCC,
The OpenOODB system at Texas Instruments,
The IRIS system developed at HP laboratories,
The ODE system at ATT Bell Labs, and
The ENCORE/ObServer project at Brown
University.
Commercially available OO systems
GEM-STONE/OPAL of SerioLogic,
ONTOS of Ontologic,
Objectivity of Objectivity Inc.,
Versant of Versant Technologies,
ObjectStore of Object Design, and,
O2 of O2 Technology.
CSCI 253 -- Database Modelling
16
Classification of DBMSs
 Classification

according to Number of Users
Single user systems support only one user at a
time and are mostly used with personal computers.

Multiuser systems, which include the majority of
DBMSs, support many users concurrently.
CSCI 253 -- Database Modelling
17
Classification of DBMSs
 Classification
according to Number of Sites
 Centralized
DBMS where the data is stored at a
single computer site. Most DBMSs are centralized.
A centralized DBMS can support multiple users, but
the DBMS and the database themselves reside totally
at a single computer site.
 Distributed
DBMS (DDBMS) can have the actual
database and DBMS software distributed over many
sites, connected by a computer network. Many
DDBMSs use a client-server architecture.
CSCI 253 -- Database Modelling
18
Thank you
CSCI 253 -- Database Modelling
19