Chapter 3

1 / 40

# Chapter 3 - PowerPoint PPT Presentation

Chapter 3. Relational Data Model. Concept. Introduction Relation Attribute,Tuple,Domain Relational Schema Database Schema The Relational Algebra Query Languages Views. Introduction.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Chapter 3' - lucita

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Chapter 3

Relational Data Model

Concept
• Introduction
• Relation
• Attribute,Tuple,Domain
• Relational Schema
• Database Schema
• The Relational Algebra
• Query Languages
• Views
Introduction
• The relational model was formally introduced by Dr. E. F. Codd in 1970 and has evolved since then, through a series of writings.
• The model provides a simple, yet rigorously defined, concept of how users perceive data.
• A relational database is a collection of two-dimensional tables.
• The organization of data into relational tables is known as the logical view of the database.
Introduction
• A relational database allows the definition of data structures, storage and retrieval operations and integrity constraints.
• In such a database the data and relations between them are organised in tables.
• A table is a collection of records and each record in a table contains the same fields.
Components of Relational Model
• Data structure: Represented in the form of tables with rows and columns
• Data manipulation: Powerful operations (SQL) used to manipulate data stored in relations
• Data integrity: Facilities are included to specify business rules that maintain the integrity of data when they are manipulated.
Attribute,Domain,Tupple
• Each attribute of a relation has a name
• The set of allowed values for each attribute is called the domain of the attribute
• Attribute values are (normally) required to be atomic, that is, indivisible
• E.g. multivalued attribute values are not atomic
• E.g. composite attribute values are not atomic
• The special value null is a member of every domain
• The null value causes complications in the definition of many operations
• we shall ignore the effect of null values in our main presentation and consider their effect later
Relation
• A named two-dimensional table of data. Each relation consists of a finite set of named columns and an arbitrary number of unnamed rows.

Notation:

RELATION NAME(Attribute1, Attribute2…)

Example:

EMPLOYEE(EmpID, Name, Dept, Salary)

Properties of Relations
• Each relation (or table) has a unique name
• Entries in columns are atomic (no repeating groups - single valued)
• Entries in columns are from the same domain
• Each row is unique (no duplicate rows)
• The sequence of columns (left to right) is insignificant
• The sequence of rows (top to bottom) is insignificant
Relational Keys
• Allow storage and retrieval of a row of data based on stored values of that data
• Primary Key: An attribute (or combination of attributes) that uniquely identify each row in a relation
• Composite Key: A primary key that consists of more than one attribute
Foreign Key
• An attribute in one table that is a primary key in another table.

Foreign Keys

Purchase Order

PO #

PODate

Vendor #

Item #

Vendor

Name

Balance

Vendor #

Item

Reord- Pt.

Name

QOH

Item #

Integrity Constraints(ràng buộc toàn vẹn)
• Facilitate maintaining accurate data
• Domain Constraints: the set of values that may be assigned to an attribute must be from the same domain
• Domain name, meaning, data type, size/length, allowable values or ranges
• Operational Constraints
Integrity Constraints
• Entity Integrity: Every relation must have a primary key and the data values of that key are valid.
• The primary key can never be null
• Referential Integrity: Either each foreign key value must match a primary key value in the other relation or the foreign key value must be null
Referential Integrity Constraints

CUSTOMER

ORDER

Order-ID Order-Date Customer-ID

ORDER LINE

Order-IDProduct-ID Quantity

PRODUCT

Product-ID Product-Description Product-Finish Unit-Price On-Hand

Well Structured Relations
• Minimum Redundancy
• Users can insert, modify and delete row (instances) without errors or inconsistencies (Anomalies).
• Anomalies: errors that may result when a user attempts to update a table that contains redundant data.
The Relational Algebra
• The relational algebra is a procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation as their result.
Relational Algebra
• Fundamental operations in the relational algebra:
• Select
• Project
• Union
• set intersection
• Cartesian product
• Division
• Join
The Union Operation
• allows to find tuples that are
• a union operation r U s is:
• r U(+) s ={t/ t r or t s}

r’

The Union Operation
• For a union operation r U s to be valid, we require that two conditions hold:
• The relations r and s must have the same number of attributes.
• The domains of the ith attribute of r and the ith attribute of s must be the same, for all i.
The Union Operation
• find the names of all bank customers who have either an account or a loan or both.
The Set-Intersection Operation
• Find all tuples that are in both relation r1 and relation r2
• r3= r1* r2 = {t/ t  r1 and t  r2}
The Set-Intersection
• find all customers who have both a loan and an account.
The Set Difference(Minus) Operation
• Allows to find tuples that are in one relation but are not in another.
• r3= r1- r2 ={t/ t  r1 and t  r2}
• Set differences must be taken between compatible relations.
• r and s must have the same arity
• attribute domains of r and s must be compatible
The Set Difference(Minus) Operation
• find all customers of the bank who have an account but not a loan
The Select Operation
• The select operation selects tuples that satisfy a given condition..
• Q+=A1,A2,...,An
• Condition D(x)
• Q' = q /q Q /q satisfy D(x)
The Select Operation
• We can find all tuples in which the amount lent is more than \$1200
The projectOperation
• Projection is the operation of selecting certain attributes from a relation R to form a new relation S.
• X, X = {A1, A2,..., An}
• r’=r.X=r[X]={t’/t  r and t’=t.X}
The projectOperation
• Find those customers who live in Harrison.
Cartesian product
• The cartesian product of two tables combines each row in one table with each row in the other table.
• Q1(A1,A2,...,Am)
• Q2( B1,B2,...,Bn),
• Q3= Q1* Q2=q3=(q1,q2)Q3/q1Q1 q2Q2
Division Operation
• Suited to queries that include the phrase “for all”.
• Let r and s be relations on schemas R and S respectively where
• R = (A1, …, Am, B1, …, Bn)
• S = (B1, …, Bn)

The result of r  s is a relation on schema

R – S = (A1, …, Am)

r  s = { t | t   R-S(r)   u  s ( tu  r ) }

S

D

E

R

A

B

C

D

E

a

1

a

a

1

b

1

a

a

1

a

b

1

a

a

1

a

b

3

a

a

1

a

b

1

a

b

1

Division Operation
The Natural-Join Opeation
• Outer Join
• left outer join
• Right Outer join
• Inner join

Inner join left outer join right outer join full outer join

Exercises –Xác định các loại quan hệ
• Cho biết các nhân viên ở phòng số 4
• Quan hệ: NHANVIEN
• Thuộc tính: PHG
• Điều kiện: PHG=4
Exercises –Xác định các loại đại số quan hệ
• Cho biết họ tên và lương của các nhân viên
• Quan hệ: NHANVIEN
• Thuộc tính: HONV, TENNV, LUONG
Exercises –Xác định các loại đại số quan hệ
• Với mỗi phòng ban, cho biết thông tin của người trưởng phòng
• Quan hệ: PHONGBAN, NHANVIEN
• Thuộc tính: TRPHG, MAPHG, TENNV, HONV, …
Exercises –Xác định các loại đại số quan hệ
• Cho biết mã nhân viên tham gia tất cả các đề án
• Quan hệ: PHANCONG, DEAN
• Thuộc tính: MANV
Exercises –Xác định các loại đại số quan hệ
• Cho biết họ tên nhân viên và tên phòng ban mà họ phụ trách nếu có
• Quan hệ: NHANVIEN, PHONGBAN
• Thuộc tinh: TENNV, TENPH
Exercises
• Quản lý điểm thi của sinh viên với các thông tin MASV, HOTEN, MONHOC,TENKHOA, DIEMTHI