chapter 3 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 3 PowerPoint Presentation
Download Presentation
Chapter 3

Loading in 2 Seconds...

play fullscreen
1 / 40

Chapter 3 - PowerPoint PPT Presentation


  • 56 Views
  • Uploaded on

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

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

PowerPoint Slideshow about 'Chapter 3' - lucita


An Image/Link below is provided (as is) to download presentation

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

Chapter 3

Relational Data Model

concept
Concept
  • Introduction
  • Relation
  • Attribute,Tuple,Domain
  • Relational Schema
  • Database Schema
  • The Relational Algebra
  • Query Languages
  • Views
introduction
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.
introduction1
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
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
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
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
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
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
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

Address

Balance

Vendor #

Item

Reord- Pt.

Name

QOH

Item #

integrity constraints r ng bu c to n v n
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
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
Referential Integrity Constraints

CUSTOMER

Customer-ID Customer-Name Customer-Address

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
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
  • 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
Relational Algebra
  • Fundamental operations in the relational algebra:
  • Select
  • Project
  • Union
  • set intersection
  • Cartesian product
  • Division
  • Join
the union operation
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 operation1
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 operation2
The Union Operation
  • find the names of all bank customers who have either an account or a loan or both.
the set intersection operation
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
The Set-Intersection
  • find all customers who have both a loan and an account.
the set difference minus operation
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 operation1
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
  • 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 operation1
The Select Operation
  • We can find all tuples in which the amount lent is more than $1200
the p roject operation
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 p roject operation1
The projectOperation
  • Find those customers who live in Harrison.
cartesian product
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
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 ) }

division operation1

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
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
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
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 h1
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 h2
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 h3
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
Exercises
  • Quản lý điểm thi của sinh viên với các thông tin MASV, HOTEN, MONHOC,TENKHOA, DIEMTHI