Fundamentals of ​Databases IT403

‫ر‬
‫الجامعة السعودية االلكتونية‬
‫ر‬
‫االلكتونية‬
‫الجامعة السعودية‬
‫‪26/12/2021‬‬
College of Computing and Informatics
Fundamentals of Databases
Fundamentals of Databases
Module 1
Databases and Database Users
Contents
1. Databases and Database Users
Weekly Learning Outcomes
1. Explain the basic concepts of Database.
2. Explain types of Database Users.
Required Reading
1. Chapter 1: The Complete chapter
(Fundamentals of Database Systems, Global Edition, 7th
Edition (2017) by Ramez Elmasri & Shamkant Navathe)
Recommended Reading
Database System Concepts and Architecture:

NoSQL databases:
This Presentation is mainly dependent on the textbook: Fundamentals of Database Systems, Global Edition, 7th Edition (2017) by Ramez Elmasri & Shamkant Navathe
• Databases and Database Users
Chapter 1 Outline
Types of Databases and Database Applications
◼ Basic Definitions
◼ Typical DBMS Functionality
◼ Example of a Database (UNIVERSITY)
◼ Main Characteristics of the Database Approach
◼ Types of Database Users
◼ Advantages of Using the Database Approach
◼ When Not to Use Databases
◼
Basic Definitions (1)
◼
Data
◼ Known facts that can be recorded and have an implicit meaning.
◼
◼
Database
◼ A collection of related data in a DBMS.
◼
◼
◼
◼
◼
Example: the names, telephone numbers, and addresses of the people you know
Example: the list of names and addresses, and computerized catalog of a large library
Defining a database
◼ Involves specifying the data types, structures, and constraints of the data to be stored in the
database.
Meta-data
◼ The database definition or descriptive information is also stored by the DBMS in the form of
a database catalog or dictionary.
Database Management System (DBMS)
◼ A computerized system that enables users to create and maintain a database. It is a generalpurpose software system that facilitates the processes of defining, constructing,
manipulating, and sharing databases among various users and applications.
Database System
◼ The database and DBMS software together; Sometimes, the application programs and
interfaces are also included.
Basic Definitions (2)
◼
◼
◼
◼
◼
◼
◼
Manipulating a database
◼ Includes querying the database to retrieve specific data, updating the database, and
generating reports from the data.
Sharing a database
◼ Allows multiple users and programs to access the database simultaneously.
Application program
◼ Accesses the database by sending queries or requests for data to the DBMS.
Query
◼ A query causes some data to be retrieved from the database.
Transaction
◼ May cause some data to be read from and some data to be written into the database.
Protection
◼ May includes system protection against hardware or software malfunction (or crashes) and
security protection against unauthorized or malicious access.
Maintenance
◼ A typical large database has a life cycle of many years, so the DBMS must be allowing the
system to evolve as requirements change over time.
Simplified database system environment
Implicit Properties of a Database
A database represents some aspect of the real world, called
the miniworld or the universe of discourse (UoD). Changes to
the miniworld are reflected in the database.
◼ A database is a logically coherent collection of data with some
inherent meaning. A random assortment of data cannot
correctly be referred to as a database.
◼ A database is designed, built, and populated with data for a
specific purpose. It has an intended group of users and some
preconceived applications in which these users are interested.
◼
Example of a Database UNIVERSITY Application (1)
◼
Mini-world for the example:
◼
Part of a UNIVERSITY environment.
◼
Some mini-world entities:
◼
INSTRUCTORs
◼ STUDENTs
◼ DEPARTMENTs
◼ COURSEs
◼ SECTIONs (of COURSEs)
Some mini-world relationships:
◼
◼
◼
◼
◼
◼
◼
◼
SECTIONs are of specific COURSEs
STUDENTs take SECTIONs
COURSEs have prerequisite COURSEs
INSTRUCTORs teach SECTIONs
COURSEs are offered by DEPARTMENTs
STUDENTs major in DEPARTMENTs
Note: The above entities and relationships are typically expressed in the ENTITYRELATIONSHIP data model
Example of a Database UNIVERSITY Application (2)
Example of a Database UNIVERSITY Application (3)
Typical DBMS Functionality
Define a particular database in terms of its data types, structures, and
constraints
◼ Construct or Load the initial database contents on a secondary storage
medium
◼ Manipulating the database:
◼
Retrieval: Querying, generating reports
◼ Modification: Insertions, deletions and updates to its content
◼ Accessing/changing the database through Web applications
◼
Processing and Sharing by a set of concurrent users and application
programs
◼ Protection or Security measures to prevent unauthorized access
◼ “Active” processing to take internal actions on data
◼ Presentation and Visualization of data
◼ Maintaining the database and associated programs over its lifetime
◼
Main Characteristics of the Database Approach (1)
◼
Self-describing nature of a database system:
A DBMS catalog stores the description of a particular database
◼ The description is called meta-data
◼ This allows the DBMS software to be integrated with different database
applications
◼
◼
Insulation between programs and data:
◼
Allows changing data structures and data storage organization without having to
change the DBMS access programs.
Accomplished through data abstraction
◼ A data model is used to hide storage details and present the users with a conceptual
view of the database.
◼
◼
Programs refer to the data model constructs rather than data storage details Called
program-data independence.
Main Characteristics of the Database Approach (2)
◼
Support of multiple views of the data:
Each user may see a different view of the database, which
describes only the data of interest to that user.
◼ Sharing of data and multi-user transaction processing:
◼
Allowing a set of user transactions to access and update the
database concurrently (at the same time).
◼ Concurrency control within the DBMS guarantees that each
transaction is correctly executed or aborted
◼ Recovery subsystem ensures each completed transaction has its
effect permanently recorded in the database
◼ OLTP (Online Transaction Processing) is a major part of database
applications (allows hundreds of concurrent transactions to
execute per second)
◼
Example of meta-date in a simplified database catalog
Types of Database Users (Actors on the scene)
Database administrators:
◼ Responsible for authorizing/controlling access to the database; coordinating
and monitoring its use; acquiring software and hardware resources; and
monitoring efficiency of operations. The DBA is accountable for security
breaches and poor system response time.
◼ Database Designers:
◼ Responsible for defining database structure, constraints, and transactions;
communicate with users to understand their needs.
◼ End-users: Use the database for queries, reports, and updating the database
content. Can be categorized into:
◼ Casual end-users: access database occasionally when needed
◼ Naïve (or Parametric) end-users: largest section of end-user population.
◼
◼
◼
Sophisticated end-users:
◼
◼
Use previously implemented and tested programs (called “canned transactions”) to
access/update the database. Examples are bank-tellers or hotel reservation clerks or sales clerks.
These include business analysts, scientists, engineers, etc. Many use tools of software packages
that work closely with the stored database.
Stand-alone end-users:
◼
Mostly maintain personal databases using ready-to-use packaged applications.
Types of Database Applications
◼
Traditional Applications:
◼
◼
Numeric and Textual Databases in Business Applications
More Recent Applications:
Multimedia Databases (images, videos, voice, etc.)
◼ Geographic Information Systems (GIS)
◼ Data Warehouses
◼ Real-time and Active Databases
◼ Many other applications
◼
Advantages of Using the Database Approach
Controlling redundancy in data storage and in development and
maintenance efforts.
◼ Restricting unauthorized access to data.
◼ Providing persistent storage for program Objects
◼ Providing Storage Structures (e.g. indexes) for efficient Query
Processing
◼ Providing backup and recovery services.
◼ Providing multiple interfaces to different classes of users.
◼ Representing complex relationships among data.
◼ Enforcing integrity constraints on the database.
◼ Permitting inferencing and actions using rules and triggers
◼ Allowing multiple “views” of the same data
◼
Additional Implications of Using the Database Approach
◼
Potential for enforcing standards:
◼
◼
Reduced application development time:
◼
◼
Storage structures may evolve to improve performance, or because of
new requirements.
Availability of up-to-date information:
◼
◼
The time needed to add each new application is reduced.
Flexibility to change data storage structures:
◼
◼
Crucial for the success of database applications in large organizations.
Standards refer to data item names, display formats, screens, report
structures, meta-data, etc.
Extremely important for on-line transaction systems such as airline, hotel, car
reservations.
Economies of scale:
◼
Wasteful overlap of resources and personnel can be avoided by consolidating
data and applications across departments.
Historical Development of Database Technology
◼
Early Database Applications using Hierarchical and Network Systems:
◼
◼
Relational Model based Systems:
◼
◼
Relational model was introduced in 1970, and heavily researched and experimented with
at IBM Research and several universities. Relational DBMS Products emerged in the early
1980s and now exist on almost all types of computers, from small personal computers to
large servers.
Object-oriented and emerging applications:
◼
◼
◼
Starting in the mid-1960s and continuing through the 1970s and 1980s. Were based on
three main paradigms: hierarchical systems, network model–based systems, and inverted
file systems.
Object Databases were introduced in late 1980s and early 1990s. Their use has not taken
off much. Many relational DBMSs have incorporated object database concepts, leading
to a new category called object-relational databases (ORDBs)
Extended relational systems add further capabilities (e.g. for multimedia data, XML,
spatial, and other data types)
Data on the Web and E-commerce Applications:
◼
◼
Starting in the 1990s, e-commerce emerged as a major application on the Web. The
critical information on e-commerce Web pages is dynamically extracted data from
DBMSs, such as flight information, product prices, and product availability.
The eXtended Markup Language (XML) is one standard for interchanging data among
various types of databases and Web pages.
Extending Database Capabilities
◼
New functionality is being added to DBMSs in the following
areas:
◼ Scientific Applications
◼ XML (eXtensible Markup Language)
◼ Image Storage and Management
◼ Audio and Video Data Management
◼ Data Warehousing and Data Mining
◼ Spatial Data Management and Geographic Information
Systems
◼ Time Series and Historical Data Management
◼ Collecting and fusing data from distributed sensors
When not to use a DBMS
◼
Main inhibitors (costs) of using a DBMS:
High initial investment and possible need for additional hardware.
◼ Overhead for providing generality, security, concurrency control, recovery,
and other functions.
◼
◼
When a DBMS may be unnecessary:
If the database and applications are simple, well defined, and not expected
to change.
◼ If there are stringent real-time requirements that may not be met because
of DBMS overhead.
◼ If access to data by multiple users is not required.
◼
◼
When no DBMS may suffice:
If the database system is not able to handle the complexity of data because of
modeling limitations
◼ If the database users need special operations not supported by the DBMS
◼ When DBMS overhead makes it impossible to achieve the needed application
performance
◼
Main Reference
1. Chapter 1: The Complete chapter
(Fundamentals of Database Systems, Global Edition, 7th
Edition (2017) by Ramez Elmasri & Shamkant Navathe)
Additional References

This Presentation is mainly dependent on the textbook: Fundamentals of Database Systems, Global Edition, 7th Edition (2017) by Ramez Elmasri & Shamkant Navathe
Thank You
Discussion Board 1
Purpose
In this discussion board, you will post your answer to the question. You will also read
and respond to other classmate’s postings. This is an excellent way for you to interact
with your colleagues and to share your thoughts about the question answer in a critical
way.
Action Items
• Access the discussion forum for this assignment by clicking on the discussion forum title.
• Click on “Create Thread.”
• Enter a title for your question in the “Subject” line.
• Type your response into the message field to the following discussion prompt:
1. Discuss three tier Client-Server DBMS architecture in relation with the
functionality of each layer. How does it improve the security of a database?
2. Respond to two other classmates’ postings by critically reviewing your classmate’s
answer and stating which points you agree or disagree with.
Submission Instructions
Post your questions and your answer to your classmates before 18/09/2024 @ 23:59
Grading Criteria (4 Marks)
2 marks: for your original post.
1 mark: for each response to another classmate posting (2 marks maximum)
*An example of unacceptable replies: “I agree with you”, “good post I like it”, “I think your post
it good” or any similar are not acceptable replies.
‫ر‬
‫الجامعة السعودية االلكتونية‬
‫ر‬
‫االلكتونية‬
‫الجامعة السعودية‬
‫‪26/12/2021‬‬
College of Computing and Informatics
Fundamentals of Databases
Fundamentals of Databases
Module 3
Relational Model
Contents
Relational Database Constraints
Weekly Learning Outcomes
Create a Relational model of a Database.
Required Reading
1. Chapter 5: The Relational Data Model and Relational
Database Constraints
Recommended Reading
Relational Model and Relational Algebra:

This Presentation is mainly dependent on the textbook: Fundamentals of Database Systems, Global Edition, 7th Edition (2017) by Ramez Elmasri & Shamkant Navathe
• The Relational Data Model and Relational Database Constraints
Relational Model Concepts and its Origin
• The formal relational Model of Data is based on the concept of a
Relation
– Has a formal mathematical foundation provided by set theory and first
order predicate logic
• In practice, there is a standard model based on SQL (Structured
Query Language)
• There are several important differences between the formal model
and the practical model, as we shall see
• The model was first proposed by Dr. E.F. Codd of IBM Research in
1970 in the following paper:
– “A Relational Model for Large Shared Data Banks,” Communications of
the ACM, June 1970
• The above paper caused a major revolution in the field of database
management
• Dr. Codd earned the coveted ACM Turing Award in 1981
Informal Definitions (1)
• Informally, a relation looks like a table of values (see Figure 3.1 on next
slide).
• A relation contains a set of rows.
• The data elements in each row represent certain facts that correspond to
a real-world entity or relationship
– In the formal model, rows are called tuples
• Each column has a column header that gives an indication of the meaning
of the data items in that column
– In the formal model, the column header is called an attribute name (or just
attribute)
Informal Definitions (2)
• Key of a Relation:
– Each row (tuple) in the table is uniquely identified by the
value of a particular attribute (or several attributes
together)
• Called the key of the relation
– In the STUDENT relation, SSN is the key
– If no attributes posses this uniqueness property, a new
attribute can be added to the relation to assign unique
row-id values (e.g. unique sequential numbers) to
identify the rows in a relation
• Called artificial key or surrogate key
Formal Definitions – Relation Schema
• Relation 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
– n is the cardinality of the relation
• Example:
CUSTOMER (Cust-id, Cust-name, Address, Phone#)
– CUSTOMER is the relation name
– The CUSTOMER relation schema (or just relation) has four
attributes: Cust-id, Cust-name, Address, Phone#
• Each attribute has a domain or a set of valid values.
– For example, the domain of Cust-id can be 6 digit numbers.
Formal Definitions – Tuple
• A tuple is an ordered set of values (enclosed in angled
brackets ‘’)
• Each value is derived from an appropriate domain.
• A row in the CUSTOMER relation is a 4-tuple and would consist
of four values, for example:
–
– Called a 4-tuple because it has 4 values
– In general, a particular relation will have n-tuples, where n is the
number of attributes for the relation
• A relation is a set of such tuples (rows)
Formal Definitions – Domain
• A domain of values can have a logical definition:
– Example: “USA_phone_numbers” are the set of 10 digit phone numbers
valid in the U.S.
• A domain also has a data-type or a format defined for it.
– The USA_phone_numbers may have a format: (ddd)ddd-dddd where each
d is a decimal digit.
– Dates have various formats such as year, month, date formatted as
yyyy-mm-dd, or as dd:mm:yyyy etc.
• The attribute name designates the role played by a domain in a relation:
– Used to interpret the meaning of the data elements corresponding to
that attribute
– Example: The domain Date may be used to define two attributes “Invoicedate” and “Payment-date” with different meanings (roles)
Formal Definitions – State of a Relation
• Formally, a relation state r(R) is a subset of the Cartesian product
of the domains of its attributes
– Each domain contains the set of all possible values the attribute can
take.
– The Cartesian product contains all possible tuples from the attribute
domains
– The relations state r(R) is the subset of tuples that represent valid
information in the mini-world at a particular time
• Formally (see Figure 3.1),
– Given relation schema R(A1, A2, ………., An)
– Relation state r(R)  dom(A1) X dom(A2) X ….X dom(An)
• r(R): is a specific state (or “instance” or “population”) of relation R
– this is a set of tuples (rows) in the relation at a particular moment
in time
– r(R) = {t1, t2, …, tn} where each ti is an n-tuple
– ti = where each vj element-of dom(Aj)
Formal Definitions – Example
• Let R(A1, A2) be a relation schema:
– Let dom(A1) = {0, 1}
– Let dom(A2) = {a, b, c}
• Then: The Cartesian product dom(A1) X dom(A2) contains all
possible tuples from these domains:
{ , , , , , }
• The relation state r(R)  dom(A1) X dom(A2)
• For example: One possible state r(R) could be { , ,
}
– This state has three 2-tuples: , ,
Relation Definitions Summary
Informal Terms
Formal Terms
Table
Relation
Column Header
Attribute
All possible Column Values or Data Type
Domain
Row
Tuple
Table Definition
Schema of a Relation
Populated Table
State of the Relation
Characteristics of a Relation (1)
• Ordering of tuples in a relation r(R):
– The tuples are not considered to be ordered, because a relation is a set of
tuples
• Ordering of attributes in a relation schema R (and of values within
each tuple):
– The attributes in R(A1, A2, …, An) and the values in each t= are
considered to be ordered
– However, a more general definition of relation does not require attribute ordering
– In this case, a tuple t = { , …, } is an unordered set of n
pairs – one pair for each of the relation attributes (see Figure 3.3)
Characteristics of Relations (2)
• Values in a tuple:
– All values are considered atomic (indivisible).
– Each value must be from the domain of the attribute for that
column
• If tuple t = is a tuple (row) in the relation state r of R(A1,
A2, …, An)
• Then each vi must be a value from dom(Ai)
– A special null value is used to represent values that are unknown
or inapplicable to certain tuples.
• Notation:
– We refer to component values of a tuple t by:
• t[Ai] or t.Ai
• This is the value vi of attribute Ai for tuple t
– Similarly, t[Au, Av, …, Aw] refers to the subtuple of t containing
the values of attributes Au, Av, …, Aw, respectively in t
Relational Integrity Constraints
• Constraints are conditions that must hold on all valid relation
states.
• Constraints are derived from the mini-world semantics
• There are three main types of built-in constraints in the
relational model:
– Key constraints
– Entity integrity constraints
– Referential integrity constraints
• Another implicit constraint is the domain constraint
– Every value in a tuple must be from the domain of its attribute
(or it could be null, if allowed for that attribute)
Key Constraints (1)
• Superkey SK of R:
– Is a set of attributes SK of R with the following condition:
• No two tuples in any valid relation state r(R) will have the same
value for SK
• That is, for any two distinct tuples t1 and t2 in r(R), t1.SK  t2.SK
• This condition must hold in any valid state r(R)
• Key (also called Candidate key) K of R:
– Is a “minimal” superkey
– Formally, a key K is a superkey such that removal of any
attribute from K results in a set of attributes that is not a
superkey (or key) any more (does not possess the superkey
uniqueness property)
– Hence, a superkey with one attribute is always a key
Key Constraints (2)
• Example: Consider the CAR relation schema:
– CAR(State, Reg#, SerialNo, Make, Model, Year)
– CAR has two keys (determined from the mini-world constraints):
• Key1 = {State, Reg#}
• Key2 = {SerialNo}
– Both are also superkeys of CAR
– However, {SerialNo, Make} is a superkey but not a key.
• 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 key
Key Constraints (3)
• If a relation has several keys, they are called candidate keys;
one is chosen to be the primary key; the others are called
unique (or secondary) keys
– The primary key attributes are underlined.
• Example: Consider the CAR relation schema:
– CAR(State, Reg#, SerialNo, Make, Model, Year)
– We choose License_number (which contains (State, Reg#)
together) as the primary key – see Figure 3.4
• The primary key value is used to uniquely identify each tuple in
a relation
– Provides the tuple identity
– Also used to reference the tuple from other tuples
• General rule: Choose the smallest-sized candidate key (in
bytes) as primary key
– Not always applicable – choice is sometimes subjective (as in
Figure 3.4 – see next slide)
Relational Database Schema
• Relational Database Schema:
– A set S of relation schemas that belong to the same database.
– S is the name of the whole database schema
– S = {R1, R2, …, Rn}
– R1, R2, …, Rn are the names of the individual relation schemas within the
database S
– Figure 3.5 shows a COMPANY database schema with 6 relation schemas
Example of Relational Database State
• Next slide show an example of a COMPANY database state
(Figure 3.6)
– Each relation has a set of tuples
• The tuples in each table satisfy key and other constraints
• If all constraints are satisfied by a database state, it is called a valid
state
– The database state changes to another state whenever the
tuples in any relation are changed via insertions, deletions,
or updates
Entity Integrity Constraint
• Entity Integrity:
– The primary key attributes PK of each relation schema R in
S cannot have null values in any tuple of r(R).
• This is because primary key values are used to identify the
individual tuples.
• t.PK  null for any tuple t in r(R)
• If PK has several attributes, null is not allowed in any of these
attributes
– Note: Other attributes of R may be also be constrained to
disallow null values (called NOT NULL constraint), even
though they are not members of the primary key.
Referential Integrity Constraint (1)
• A constraint involving two relations
– The previous constraints (key, entity integrity) 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
• Referential integrity can be displayed as a directed arc from
R1.FK to R2.PK – see Figure 3.7
Referential Integrity (or foreign key) Constraint (2)
• Statement of the constraint
– For a particular database state, the value of the foreign key
attribute (or attributes) FK in each tuple of the referencing
relation R1 can be either:
• (1) An existing primary key (PK) value of a tuple in the referenced
relation R2, or
• (2) a null.
• In case (2), the FK in R1 should not be a part of its
own primary key, and cannot have the NOT NULL
constraint.
Other Types of Constraints
• Semantic Integrity Constraints:
– cannot be expressed by the built-in model constraints
– Example: “the max. no. of hours per employee for all
projects he or she works on is 56 hrs per week”
• A constraint specification language can be used to
express these
• SQL has TRIGGERS and ASSERTIONS to express some
of these constraints
Operations to Modify Relations (1)
• Each relation will have many tuples in its current relation state
• The relational database state is a union of all the individual relation
states at a particular time
• Whenever the database is changed, a new state arises
• Basic operations for changing the database:
– INSERT new tuples in a relation
– DELETE existing tuples from a relation
– UPDATE attribute values of existing tuples
• Integrity constraints should not be violated by the update
operations.
• Several update operations may have to be grouped together into a
transaction.
Operations to Modify Relations (2)
• Updates may propagate to cause other updates
automatically. This may be necessary to maintain
integrity constraints.
• In case of integrity violation, several actions can be
taken:
– Cancel the operation that causes the violation (RESTRICT or
REJECT option)
– Perform the operation but inform the user of the violation
– Trigger additional updates so the violation is corrected
(CASCADE option, SET NULL option)
– Execute a user-specified error-correction routine
INSERT operation
• INSERT one or more new tuples into a relation
• INSERT may violate any of the constraints:
– Domain constraint:
• if one of the attribute values provided for a new tuple is not of the
specified attribute domain
– Key constraint:
• if the value of a key attribute in a new tuple already exists in
another tuple in the relation
– Referential integrity:
• if a foreign key value in a new tuple references a primary key value
that does not exist in the referenced relation
– Entity integrity:
• if the primary key value is null in a new tuple
DELETE operation
• DELETE one or more existing tuples from a relation
• DELETE may violate only referential integrity:
– If the primary key value of the tuple being deleted is referenced
from other tuples in the database
• Can be remedied by several actions: RESTRICT, CASCADE, SET NULL
– RESTRICT option: reject the deletion
– CASCADE option: propagate the deletion by automatically deleting
the referencing tuples
– SET NULL option: set the foreign keys of the referencing tuples to
NULL (the foreign keys cannot have NOT NULL constraint)
– One of the above options must be specified during database
design for each referential integrity (foreign key) constraint
UPDATE operation
• UPDATE modifies the values of attributes in one or more
existing tuples in a relation
• UPDATE may violate domain constraint and NOT NULL
constraint on an attribute being modified
• Other constraints may also be violated:
– Updating the primary key (PK):
• Similar to a DELETE followed by an INSERT
• Need to specify similar options to DELETE
• The CASCADE option propagates the new value of PK to the foreign
keys of the referencing tuples automatically
– Updating a foreign key (FK) may violate referential integrity
– Updating an ordinary attribute (neither PK nor FK):
• Can only violate domain or NOT NULL constraints
Main Reference
1. Chapter 5: The Relational Data Model and Relational
Database Constraints
(Fundamentals of Database Systems, Global Edition, 7th
Edition (2017) by Ramez Elmasri & Shamkant Navathe)
Additional References

This Presentation is mainly dependent on the textbook: Fundamentals of Database Systems, Global Edition, 7th Edition (2017) by Ramez Elmasri & Shamkant Navathe
Thank You
‫ر‬
‫الجامعة السعودية االلكتونية‬
‫ر‬
‫االلكتونية‬
‫الجامعة السعودية‬
‫‪26/12/2021‬‬
College of Computing and Informatics
Fundamentals of Databases
Fundamentals of Databases
Module 4
Entity–Relationship Model-1
Contents
1. Using High-Level Conceptual Data Models for Database Design
2. A Sample Database Application
3. Entity Types, Entity Sets, Attributes, and Keys
4. Relationship Types, Relationship Sets, Roles, and Structural
Constraints
5. Weak Entity Types
6. Refining the ER Design for the COMPANY Database
Weekly Learning Outcomes
1. Explain the Entity-Relationship model.
Required Reading
1.
2.
3.
4.
5.
6.
Chapter 3: Using High-Level Conceptual Data Models for Database Design
Chapter 3: A Sample Database Application
Chapter 3: Entity Types, Entity Sets, Attributes, and Keys
Chapter 3: Relationship Types, Relationship Sets, Roles, and Structural Constraints
Chapter 3: Weak Entity Types
Chapter 3: Refining the ER Design for the COMPANY Database
(Fundamentals of Database Systems, Global Edition, 7th Edition (2017) by Ramez Elmasri &
Shamkant Navathe)
Recommended Reading
Entity–Relationship Model-1:

This Presentation is mainly dependent on the textbook: Fundamentals of Database Systems, Global Edition, 7th Edition (2017) by Ramez Elmasri & Shamkant Navathe
• Using High-Level Conceptual Data Models for Database Design
Overview of the Database Design Process
• There are two main activities (see Figure 7.1 in the
next slide):
– Database schema design
– Application programs design
• Focus will be on database schema design
– Given the database requirements, design the
conceptual schema for a database
• Application programs design focuses on the programs
and interfaces that access and update the database
(considered part of software engineering discipline)
The Database Design Process
• Requirements Analysis and Specification is in the realm of
Systems Analysis and Design
• This and next chapter focus on Conceptual Design (see
Figure 7.1)
• Physical Design and Logical Design will not be
discussed here.
The Entity-Relationship (ER) Model
• ER model is a conceptual data model for database design
– Has an associated notation (ER schema diagrams) for
drawing/displaying the database schema
– Many variations of ER model exists
– Also, many extensions (E.g. EER model)
• Next slide (Figure 7.2) shows a complete ER schema diagram
for a COMPANY database
– We will explain gradually how this design is created
– First we introduce the requirements for the COMPANY database
– Then we present ER model concepts and diagrammatic notation
gradually, and design the schema step-by-step
• A Sample Database Application
Example: COMPANY Database (1)
• Create a database schema design based on the
following (simplified) requirements for a COMPANY
Database:
– The company is organized into DEPARTMENTs. Each
department has a name, number and an employee
who manages the department. We keep track of the
start date of the department manager. A department
may have several locations.
– Each department controls a number of PROJECTs. Each
project has a unique name, unique number and is
located at a single location.
COMPANY Database (cont.)
– The database will store each EMPLOYEE’s name, social
security number (unique for each employee), address,
salary, sex, and birthdate.
• Each employee works for one department, but may work on several
projects.
• We keep track of the number of hours per week that an employee
currently works on each project.
• We also keep track of the direct supervisor of each employee.
– An employee can have DEPENDENTs.
• For each dependent, the database keeps track of their first name,
sex, birthdate, and their relationship to the employee (child,
spouse, etc.).
• Entity Types, Entity Sets, Attributes, and Keys
ER Model Concepts
• Entities and Attributes
– Entities: Specific objects or things in the mini-world that are
represented in the database.
• Examples: the EMPLOYEE John Smith, the Research DEPARTMENT,
the ProductX PROJECT
– Attributes: Properties used to describe an entity.
• Examples: an EMPLOYEE entity may have the attributes Name, SSN,
Address, Sex, BirthDate
– Data values: A specific entity has a value for each of its
attributes.
• Example: An employee entity may have Name=”John Smith”,
SSN=’123456789′, Address=”731, Fondren, Houston, TX”, Sex=’M’,
BirthDate=”09-JAN-55‘
– Each attribute has a value set (or data type) associated with it –
e.g. integer, string, subrange, enumerated type, …
Types of Attributes (1)
• Simple attribute (sometimes called atomic):
– Each entity has a single value for the attribute. For example, the SSN
or Sex of an employee.
• Composite attribute (also called compound):
– The attribute may be composed of several components. For
example:
• Address(Apt#, House#, Street, City, State, ZipCode, Country), or
• Name(FirstName, MiddleName, LastName).
• Composition may form a hierarchy where some components are
themselves composite (Figure 7.4, next slide).
• Multi-valued attribute (also called repeating group or
collection):
– An single entity may have multiple values for that attribute. For
example, Color of a CAR or PreviousDegrees of a STUDENT.
• Denoted as {Color} or {PreviousDegrees}.
Types of Attributes (2)
• Composite and multi-valued attributes may be nested
(to any number of levels).
– Example: PreviousDegrees of a STUDENT can be a
composite multi-valued attribute denoted by
{PreviousDegrees (College, Year, Degree, Field)}
– Multiple PreviousDegrees values can exist for a
particular student
– Each has four subcomponent attributes:
• College, Year, Degree, Field
Entity Types and Key Attributes (1)
• Entities with the same basic attributes are grouped
(or typed) into an entity type.
– Examples: EMPLOYEE or PROJECT.
• Key attribute: an attribute of an entity type for which
each entity must have a unique (distinct) value.
– Example: SSN of EMPLOYEE, or PNUMBER of
PROJECT, or PNAME of PROJECT.
Entity Types and Key Attributes (2)
• A key attribute may be composite.
– Example: VehicleTagNumber (also known as
LicensePlateNo) of a CAR is a key with two
components (LicNumber, State).
• An entity type may have more than one key.
– The CAR entity type may have two keys:
• VehicleIdentificationNumber (popularly called VIN, unique
number stamped on each new car)
• VehicleTagNumber (Number, State)
• Each key is underlined in ER diagrams (see next
slides)
Displaying an Entity type
• In ER diagrams, the entity type name is displayed in a
rectangular box
• Attributes are displayed in ovals
– Each attribute is connected to its entity type
– Components of a composite attribute are connected to the
oval representing the composite attribute
– Each key attribute is underlined
– Multivalued attributes displayed in double ovals
• See CAR example (Figure 3.7(a)) on next slide
Entity Set
• Each entity type will have a collection of individual
entities stored in the database
– Called the entity set
– Previous slide (Figure 3.7(b) show three CAR entities in the
entity set for CAR
– Same name (CAR) refers to both entity type and entity set
– Object models (see Chapter 11) give different names to the
entity type and the entity set
– Entity set changes over time as entities are created and
deleted – represents current state of database
Initial Design of Entity Types for the COMPANY
Database Schema
• Based on the requirements, we can identify four initial
entity types in the COMPANY database:
– DEPARTMENT
– PROJECT
– EMPLOYEE
– DEPENDENT
• Initial design (Figure 3.8) on following slide, will be
refined into final design
• Initial attributes shown are derived from the
requirements description
• Relationship Types, Relationship Sets, Roles, and Structural
Constraints
Refining the initial design by introducing Relationships
• The initial design is typically not complete
• Some aspects in the requirements will be
represented as relationships
• ER model has three main concepts:
– Entities (and their entity types and entity sets)
– Attributes (simple, composite, multivalued)
– Relationships (and their relationship types and
relationship sets)
• We introduce relationship concepts next
Relationships and Relationship Types
• A relationship relates two or more distinct entities, with a
specific meaning.
– For example, EMPLOYEE John Smith works on the ProductX
PROJECT, or EMPLOYEE Franklin Wong manages the Research
DEPARTMENT.
• Relationships of the same type are grouped or typed into a
relationship type.
– For example, the WORKS_ON relationship type in which EMPLOYEEs
and PROJECTs participate, or the MANAGES relationship type in
which EMPLOYEEs and DEPARTMENTs participate.
• The degree of a relationship type is the number of
participating entity types.
– Both MANAGES and WORKS_ON are binary relationships.
Relationship Type vs. Relationship Set
• Relationship Type:
– Is the schema description of a relationship
– Identifies the relationship name and the participating
entity types
– Also identifies certain relationship constraints
• Relationship Set:
– The current set of relationship instances represented in the
database
– The current state of a relationship type
Relationship Set
• A set of associations (or relationship instances)
between individual entities from the participating
entity sets:
– Example: Figure 3.9 (next slide) shows a relationship
set for WORKS_FOR
– {r1, r2, r3, r4, r5, r6, r7, …}
– Relationship instance r1=(e1, d1) means EMPLOYEE e1
WORKS_FOR DEPARTMENT d1
– Associates e1 with d1
Relationship Type
• Previous figure displayed the relationship set
• Each instance in the set relates individual participating
entities – one from each participating entity type
• In ER diagrams, we represent the relationship type as
follows:
– Diamond-shaped box is used to display a relationship type
– Connected to the participating entity types via straight lines
– Degree of a relationship type is the number of participating
entity types
Refining the COMPANY Initial Design by
Including Relationships
• By examining the requirements, attributes in the initial design
that refer to other entities are converted into relationships
(and removed from the entity types)
• Some of these relationship attributes (Figure 7.8, repeated on
next slide) are:
– The Department attribute of EMPLOYEE refers to the
DEPARTMENT entity that the employee WORKS_FOR
– The Manager attribute of DEPARTMENT refers to the EMPLOYEE
entity who MANAGES the DEPARTMENT
– The Supervisor attribute of EMPLOYEE refers to another
EMPLOYEE entity (this is called a recursive relationship)
– Several other similar attributes are converted into relationships –
can you identify those in next slide?
Refining the COMPANY Initial Design by
Including Relationships (2)
• Six relationship types are identified for the COMPANY
database schema (see Figure 3.2, repeated next slide)
• All are binary relationships (degree 2)
• Listed below with their participating entity types:
– WORKS_FOR (between EMPLOYEE, DEPARTMENT)
– MANAGES (also between EMPLOYEE, DEPARTMENT)
– CONTROLS (between DEPARTMENT, PROJECT)
– WORKS_ON (between EMPLOYEE, PROJECT)
– SUPERVISION (between EMPLOYEE (as subordinate), EMPLOYEE
(as supervisor))
– DEPENDENTS_OF (between EMPLOYEE, DEPENDENT)
Discussion on Relationship Types
• In the refined design, some attributes from the initial entity
types are refined into relationships:
– Manager of DEPARTMENT -> MANAGES
– Works_on of EMPLOYEE -> WORKS_ON
– Department of EMPLOYEE -> WORKS_FOR
– etc
• In general, more than one relationship type can exist between
the same participating entity types
– MANAGES and WORKS_FOR are distinct relationship types
between EMPLOYEE and DEPARTMENT
– Different meanings and different relationship instances.
Recursive Relationship Type
• A relationship type with the same entity type participating
twice in two distinct roles
• Example: the SUPERVISION relationship
• EMPLOYEE participates twice in two distinct roles:
– supervisor (or boss) role
– supervisee (or subordinate) role
– must distinguish the roles in a relationship instance
• Each relationship instance ri relates two distinct EMPLOYEE
entities (see Figure 3.11, next slide):
– One employee in supervisor role (labeled 1 in Fig. 3.11)
– One employee in supervisee role (labeled 2 in Fig. 3.11)
• Weak Entity Types
Weak Entity Types
• An entity type that does not have a key attribute on its own
• A weak entity must participate in an identifying relationship type with an
owner (or identifying) entity type
• Individual entities are identified by the combination of:
– A partial key of the weak entity type
– The particular entity they are related to in the identifying entity type
• Example (see Figure 7.2):
– A DEPENDENT entity is identified by the dependent’s first name, and
the specific EMPLOYEE with whom the dependent is related
– Name of DEPENDENT is the partial key
– DEPENDENT is a weak entity type
– EMPLOYEE is its identifying (owner) entity type via the identifying
relationship type DEPENDENT_OF
• Refining the ER Design for the COMPANY Database
Main Reference
1.
2.
3.
4.
5.
6.
Chapter 3: Using High-Level Conceptual Data Models for Database Design (3.1)
Chapter 3: A Sample Database Application (3.2)
Chapter 3: Entity Types, Entity Sets, Attributes, and Keys (3.3)
Chapter 3: Relationship Types, Relationship Sets, Roles, and Structural Constraints (3.4)
Chapter 3: Weak Entity Types (3.5)
Chapter 3: Refining the ER Design for the COMPANY Database (3.6)
(Fundamentals of Database Systems, Global Edition, 7th Edition (2017) by Ramez Elmasri &
Shamkant Navathe)
Additional References

id=dc1635b3-88ab-4a84-814c-5908ea9c232c%40sdc-v-sessmgr01
This Presentation is mainly dependent on the textbook: Fundamentals of Database Systems, Global Edition, 7th Edition (2017) by Ramez Elmasri & Shamkant Navathe
Thank You
‫ر‬
‫الجامعة السعودية االلكتونية‬
‫ر‬
‫االلكتونية‬
‫الجامعة السعودية‬
‫‪26/12/2021‬‬
College of Computing and Informatics
Fundamentals of Databases
Fundamentals of Databases
Module 2
Database System Concepts and Architecture
Contents
1. Database System Concepts and Architecture
Weekly Learning Outcomes
1. Explain Database System Concepts and Architecture.
Required Reading
1. Chapter 2: Database System Concepts and Architecture
(Fundamentals of Database Systems, Global Edition, 7th
Edition (2017) by Ramez Elmasri & Shamkant Navathe)
Recommended Reading
Database System Concepts and Architecture:

This Presentation is mainly dependent on the textbook: Fundamentals of Database Systems, Global Edition, 7th Edition (2017) by Ramez Elmasri & Shamkant Navathe
• Database System Concepts and Architecture
Data Models
• Data Model:
– A set of concepts to describe the structure of a database, the
operations for manipulating the data, and the constraints that the data
should follow.
• Data Model Structure and Constraints:
– Data Model constructs define the database structure
– Data model constructs often include: data elements and their data
types (often called attributes); grouping of related elements into
entities (also called objects or records or tuples); and relationships
among entities
– Constraints specify restrictions on the stored data; the data that
satisfies the constraints is called valid data
• Data Model Operations:
– These operations are used for specifying database retrievals and
updates by referring to the constructs of the data model.
– Operations on the data model may include basic model
operations (e.g. generic insert, delete, update) and user-defined
operations (e.g. compute_student_gpa, update_inventory)
Categories of Data Models
• Conceptual (high-level, semantic) data models:
– Provide concepts that are close to the way many users perceive
data.
• (Also called entity-based or object-based data models.)
• Physical (low-level, internal) data models:
– Provide concepts that describe details of how data is stored in
the computer. These are usually specified in an ad-hoc manner
through DBMS design and administration manuals
• Implementation (representational) data models:
– Provide concepts that fall between the above two, used by many
commercial DBMS implementations (e.g. relational data models
used in many commercial systems).
Database Schema versus Database State (1)
• Database Schema:
– The description of a database.
– Includes descriptions of the database structure, relationships, data types, and
constraints
• Schema Diagram:
– An illustrative display of a database schema
• Schema Construct:
– A component of the schema or an object in the schema, e.g., STUDENT,
COURSE, Name
• Database State:
– The actual data stored in a database at a particular moment in time. This
includes the collection of all the data in the database.
– Also called a database instance, occurrence, or snapshot.
Database Schema vs. Database State (2)
• Initial Database State:
– Refers to the database state when it is initially loaded into
the system.
• Valid State:
– A state that satisfies the structure and constraints of the
database.
• Distinction
– The database schema changes very infrequently.
– The database state changes every time the database is
updated.
• Schema is also called intension.
• State is also called extension.
Example of a Database Schema
Three-Schema Architecture (1)
• Proposed to support DBMS characteristics of:
– Program-data independence.
– Support of multiple views of the data.
• Not explicitly used in commercial DBMS products, but has
been useful in explaining database system organization
• Defines DBMS schemas at three levels:
– Internal schema at the internal level to describe physical storage
structures and access paths (e.g. indexes).
• Typically uses a physical data model.
– Conceptual schema at the conceptual level to describe the structure
and constraints for the whole database for a community of users. Uses
an implementation (or a conceptual) data model.
– External schemas at the external level to describe the various user
views.
• Usually uses the same data model as the conceptual schema.
Three-Schema Architecture (2)
• Mappings among schema levels are needed to
transform requests and data.
– Users and programs refer to an external schema, and are
mapped by the DBMS to the internal schema for execution.
– Data extracted from the internal DBMS level is reformatted
to match the user’s external view (e.g. formatting the
results of an SQL query for display as a Web page)
Data Independence
• Logical Data Independence:
– The capacity to change the conceptual schema without having to
change the external schemas and their associated application
programs.
• Physical Data Independence:
– The capacity to change the internal schema without having to change
the conceptual schema.
– For example, the internal schema may be changed when certain file
structures are reorganized or new indexes are created to improve
database performance
• When a schema at a lower level is changed, only the mappings
between this schema and higher-level schemas need to be
changed in a DBMS that fully supports data independence.
• The higher-level schemas themselves are unchanged.
– Hence, the application programs need not be changed since they refer
to the external schemas.
DBMS Languages (DDL)
• Data Definition Language (DDL):
– Used by the DBA and database designers to specify the
conceptual schema of a database.
– In many DBMSs, the DDL is also used to define internal
and external schemas (views).
– Theoretically, separate storage definition language
(SDL) and view definition language (VDL) can used to
define internal and external schemas. In practice:
• SDL is typically realized via DBMS commands provided to the
DBA and database designers
• VDL is typically part of the same language as DDL
DBMS Languages (DML)
• Data Manipulation Language (DML):
– Used to specify database retrievals and updates
– DML commands (data sublanguage) can be embedded in a generalpurpose programming language (host language), such as COBOL, C,
C++, or Java
• A library of functions can also be provided to access the DBMS from a
programming language
– Alternatively, stand-alone DML commands can be applied directly
(called a query language).
• Types of DML:
– High-Level
Declarative
(Set-oriented,
Non-procedural)
Languages, such as the relational language SQL
• Specify “what” data to retrieve rather then “how” to retrieve it
• May be used in a standalone way or may be embedded in a programming
language
– Low Level or Procedural (Record-at-a-time) Languages:
• Must be embedded in a programming language
• Need programming language constructs such as looping
DBMS Interfaces
• Stand-alone query language interfaces
– Example: Typing SQL queries directly through the DBMS interactive SQL
interface (e.g. SQL*Plus in ORACLE)
• Programmer interfaces for embedding DML in programming
languages
• User-friendly interfaces (often Web-based)
– Menu-based, forms-based, graphics-based, etc.
• Menu-based, popular for browsing on the web
• Forms-based, designed for naïve users
• Graphics-based
• Supports Point and Click, Drag and Drop, etc.
• Natural language: requests in written English
• Combinations of the above:
• For example, both menus and forms used extensively in Web database interfaces
DBMS Programming Language Interfaces
• Programmer interfaces for embedding DML in a
programming language:
– Embedded Approach: e.g. embedded SQL (for C, C++,
etc.), SQLJ (for Java)
– Procedure Call Approach: e.g. JDBC for Java, ODBC for
other programming languages
– Database Programming Language Approach: e.g.
ORACLE has PL/SQL, a programming language based
on SQL; language incorporates SQL and its data types
as integral components
Other DBMS Interfaces
• Speech as Input and Output
• Web Browser as an interface
• Parametric interfaces, e.g., bank tellers using function
keys.
• Interfaces for the DBA:
• Creating user accounts, granting authorizations
• Setting system parameters
• Changing schemas or storage structures/access paths (physical
database)
Database System Utilities
• To perform certain functions such as:
– Loading data stored in files into a database; includes data
conversion tools.
– Backing up the database periodically on tape.
– Reorganizing database file structures.
– Report generation utilities.
– Performance monitoring utilities.
– Other functions, such as sorting, user monitoring, data
compression, etc.
Other Tools
• Data dictionary/repository:
– Used to store schema descriptions and other information such as
design decisions, application program descriptions, user
information, usage standards, etc.
– Active data dictionary is accessed by DBMS software and
users/DBA.
– Passive data dictionary is accessed by users/DBA only.
• Application Development Environments and CASE
(Computer-aided software engineering) tools often have
a database design component
• Examples:
– PowerBuilder (Sybase)
– JBuilder (Borland)
– JDeveloper 10G (Oracle)
Typical DBMS Component Modules
DBMS Architectures
• Centralized DBMS Architecture:
– Combines everything into single computer system, including:
DBMS software, hardware, application programs, and user
interface processing software.
– User can still connect through a remote terminal – however, all
processing is done at centralized site (computer).
• Basic 2-tier Client-Server Architecture: Specialized Server
nodes with Specialized functions
– Print server
– File server
– DBMS server
– Web server
– Email server
• Client nodes can access the specialized servers as needed
A Physical Centralized Architecture
Logical two-tier client server architecture
DBMS Server
• Provides database query and transaction services to the clients
• Relational DBMS servers are often called SQL servers, query
servers, or transaction servers
• Applications running on clients utilize an Application Program
Interface (API) to access server databases via standard
interface such as:
– ODBC: Open Database Connectivity standard
– JDBC: for Java programming access
• Client and server must install appropriate client module and
server module software for ODBC or JDBC
Client nodes
• Provide appropriate interfaces through a client
software module to access and utilize the various
server resources.
• Clients may be PCs or Workstations (or even diskless
machines) with the client software installed.
• Connected to the servers via a network.
– LAN: local area network
– wireless network
Two Tier Client-Server DBMS Architecture
• A program running at a client may connect to several
DBMSs (also called data sources).
• In general, data sources can be files or other nonDBMS software that manages data.
• Client focuses on user interface interactions and only
accesses database when needed.
• In some cases (e.g. some object DBMSs), more
functionality is transferred to clients (e.g. data
dictionary functions, optimization and recovery
across multiple servers, etc.)
Three Tier Client-Server DBMS Architecture
• Common for Web applications
• Third intermediate layer (middle tier) called Application Server
or Web Server:
– Stores the web connectivity software and the business logic part
of the application
– Accesses and updates data on the database server
– Acts like a conduit for sending partially processed data between
the database server and the client.
• Three-tier Architecture Can Enhance Security:
– Database server only accessible via middle tier
– Clients cannot directly access database server
Three-tier client-server architecture
• Classification of Database Management Systems
Classification of DBMSs
•
Based on the data model used
– Traditional: Relational, Hierarchical, Network.
– Emerging: Object-oriented, Object-relational, NOSQL, Key-value.
•
Based on allowed users
– 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)
•
Based on number of sites
– A DBMS is centralized if the data is stored at a single computer site.
– A distributed DBMS (DDBMS) can have the actual database and DBMS software
distributed over many sites connected by a computer network.
•
Based on type of DBMS software
– Homogeneous DDBMSs use the same DBMS software at all the sites.
– Heterogeneous DDBMSs can use different DBMS software at each site. This may
further lead to federated DBMS.
•
Big data systems (NOSQL systems)
–
–
–
–
Key-value model
Document-based model
Graph-based model
Column-based model
Cost considerations for DBMSs
• Cost Range: from free open-source systems to configurations
costing millions of dollars
• Examples of free relational DBMSs: MySQL, PostgreSQL, others
• Commercial DBMSs offer additional specialized modules, e.g.
time-series module, spatial data module, document module,
XML module
– These offer additional specialized functionality when purchased
separately
– Sometimes called cartridges (e.g., in Oracle) or blades
• Different licensing options: site license, maximum number of
concurrent users (seat license), single user, etc.
History of Data Models (1)
1. Network Model
2. Hierarchical Model
3. Relational Model
4. Object-oriented Data Models
5. Object-Relational Models
• Network Model:
– The first network DBMS was implemented by Honeywell in 196465 (IDS System).
– Adopted heavily due to the standard support by CODASYL
(Conference on Data Systems Languages) (CODASYL – DBTG report
of 1971).
– Later implemented in a large variety of systems – IDMS (Cullinet now Computer Associates), DMS 1100 (Unisys), IMAGE (H.P.
(Hewlett-Packard)), VAX -DBMS (Digital Equipment Corp., next
COMPAQ, now H.P.).
Example of Network Model Schema
Network Model
• Advantages:
– Can model complex relationships among records and represents
semantics of add/delete on the relationships.
– Can handle most situations for modeling using record types and
relationship types.
– Language is navigational; uses constructs like FIND, FIND
member, FIND owner, FIND NEXT within set, GET, etc.
• Programmers can do optimal navigation through the database.
• Disadvantages:
– Navigational and procedural nature of processing requires
programming access
– Intermixes storage structures with conceptual modeling
relationships
– Database contains a complex array of pointers that thread
through a set of records.
• Little scope for automated “query optimization”
Hierarchical Model
• Hierarchical Data Model:
– Initially implemented in a joint effort by IBM and North American Rockwell
around 1965. Resulted in the IMS family of systems.
– IBM’s IMS product had a very large customer base worldwide
– Hierarchical model was formalized based on the IMS system
– Other systems based on this model: System 2k (SAS inc.)
• Advantages:
– Can implement certain tasks very efficiently
– Easy to store hierarchically organized data, e.g., organization (“org”) charts
• Disadvantages:
– Navigational and procedural nature of processing
– Difficult to store databases where multiple relationships exist among the
data records
– Little scope for “query optimization” by system (programmer must
optimize the programs)
– Language is procedural: Uses constructs like GET, GET UNIQUE, GET NEXT,
GET NEXT WITHIN PARENT, etc.
Relational Model
• Relational Model:
– Proposed in 1970 by E.F. Codd (IBM), first commercial systems in
early 1980s.
– Now in many commercial products (e.g. DB2, ORACLE, MS SQL
Server, SYBASE, INFORMIX).
– Several free open source implementations, e.g. MySQL,
PostgreSQL
– Currently most dominant for developing database applications.
– SQL relational standards: SQL-89 (SQL1), SQL-92 (SQL2), SQL-99,
SQL3, SQL-2008
– Chapters 3 through 6 describe this model in detail
Object-oriented Models
• Object-oriented Data Models:
– Allow databases to be used seamlessly with object-oriented
programming languages.
– Can store persistent objects created in O-O Programming
Languages such as C++ (e.g., in OBJECTSTORE or VERSANT), and
Smalltalk (e.g., in GEMSTONE).
– Other experimental systems include O2, ORION (at MCC – then
ITASCA), IRIS (at H.P.- used in Open OODB).
– Object Database Standard: ODMG-93, ODMG-version 2.0,
ODMG-version 3.0.
Object-Relational Models
• Object-Relational Models:
– Relational systems incorporated concepts from object
databases leading to object-relational.
– Exemplified in the latest versions of Oracle-10i, DB2, and
SQL Server and other DBMSs.
– Standards started in SQL-99 and enhanced in SQL-2008.
Main Reference
1. Chapter 2: Database System Concepts and Architecture
(Fundamentals of Database Systems, Global Edition, 7th
Edition (2017) by Ramez Elmasri & Shamkant Navathe)
Additional References

This Presentation is mainly dependent on the textbook: Fundamentals of Database Systems, Global Edition, 7th Edition (2017) by Ramez Elmasri & Shamkant Navathe
Thank You

Purchase answer to see full
attachment