Download - Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model
![Page 1: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/1.jpg)
Chapter 4
Rational data model
![Page 2: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/2.jpg)
Chapter 5Rational data model
Chapter 4Rational
data model
![Page 3: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/3.jpg)
Contents
Relational Model Concepts Relational Model Constraints and Relational
Database Schemas
![Page 4: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/4.jpg)
![Page 5: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/5.jpg)
The things you should know…
The relational Model of Data is based on the concept of a Relation.
The basic principle of relational database is proposed by E.F.Codd in 1970.
The first RDBMS production is System R The most popular RDBMS
DB2, Oracle, Ingres, Sybase, Informix,…
![Page 6: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/6.jpg)
Informal Definitions
RELATION: A table of values A relation may be thought of as a set of rows.
Each row represents a fact that corresponds to a real-world entity or relationship.
Each row has a value of an item or set of items that uniquely identifies that row in the table.
Each column typically is called by its column name or column header or attribute name.
![Page 7: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/7.jpg)
Informal Definitions
Key of a Relation: Each row has a value of a data item (or set of items) that
uniquely identifies that row in the table Called the key
In the STUDENT table, SSN is the key
Sometimes row-ids or sequential numbers are assigned as keys to identify the rows in a table Called artificial key
![Page 8: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/8.jpg)
Example - Figure 5.1
![Page 9: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/9.jpg)
Basic of the Relational Model
The relational model gives us a single way to represent data: as a two- dimensional table called a relation.
![Page 10: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/10.jpg)
Attributes ( 属性 ) Attributes of a relation serve us as names for the col
umns of the relation. Usually, the attributes describes the meaning of enti
tes in the column below.
![Page 11: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/11.jpg)
Schemas (模式) The name of a relation and the set of attributes for a
relation is called the schema for that relation. We show the schema for the relation with the relati
on name followed by a parenthesized list of its attributes.
![Page 12: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/12.jpg)
Tuples (元组) The rows of a relation, other than the header row cont
aining the attributes, are called tuple. For example:
Relations, however, are sets of tuples, and it is impossible for a tuple to appear more than once in a given relation.
![Page 13: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/13.jpg)
Domains (域) The relational model requires that each component of e
ach tuple be atomic; that is, it must be of some elementary type such as integers or string.
Each attributes of a relation has particular elementary type, thus domain is decided.
![Page 14: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/14.jpg)
Formal Definitions
A domain has 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. For example, the USA_phone_numbers may have a forma
t: (ddd)-ddd-dddd where each d is a decimal digit.
Dates have various formats such as month, date, year or yyyy-mm-dd, or dd mm,yyyy etc.
![Page 15: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/15.jpg)
Equivalent Representations of a Relation The attributes of the relation can be reordered without
change the relation.
![Page 16: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/16.jpg)
Relation Instance (关系实例) A relation about movies is not static; rather, relatio
ns change over time. It is not common for the schema of a relation to cha
nge. We shall call a set of tuples for a given relation an i
nstance of that relation.
![Page 17: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/17.jpg)
DEFINITION SUMMARY
Informal Terms Formal Terms
Table Relation
Column Attribute/Domain
Row Tuple
Values in a column Domain
Table Definition Schema of a Relation
Populated Table Extension
![Page 18: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/18.jpg)
An Example of Relation InstanceRelation: Person (Name, Address, Telephone)
Relation Instance:
Name Address Telephone
Bob 123 Main St 555-1234
Bob 128 Main St 555-1235
Pat 123 Main St 555-1235
Harry 456 Main St 555-2221
Sally 456 Main St 555-2221
Sally 456 Main St 555-2223
Pat 12 State St 555-1235
![Page 19: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/19.jpg)
More …
Relation (Instance) = a set of tuples Database = collection of relations Relation schema = relation name + attributes
Example: Movies (title, year, length, fileType)
Database schema = a set of all relation schemas Movies(Title, Year, Length, FileType) Star(Name, Age) Studio(StudioName, Addr)
![Page 20: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/20.jpg)
Name Addr Tel N1 A1 T1 N2 A2 T2 N3 A3 T3 N4 T4 N5 T5 T6 T7
Name Addr Tel N1 A1 T1 N1 A1 T2 N1 A1 T3 . . .
N1 A1 T7 N1 A2 T1 N1 A3 T1 N2 A1 T1
Tuple
Domain
Component
Attribute
![Page 21: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/21.jpg)
Characteristics of Relations
Ordering of tuples in a relation r(R): The tuples are not considered to be ordered, even thou
gh they appear to be in the tabular form.
Ordering of attributes in a relation schema R (and of values within each tuple): We will consider the attributes in R(A1, A2, ..., An) and th
e values in t=<v1, v2, ..., vn> not to be ordered .
Values in a tuple: All values are considered atomic (indivisible). A special null value is used to represent values that are unknown or inapplicable to certain tuples.
![Page 22: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/22.jpg)
Chapter 5-22
Key
Example: The STUDENT relation schema:STUDENT(stu_num, stu_name, gender, major,
Year)has two keys Key1 = {stu_num}, Key2 = {stu_na
me}candidate keys:just keysIf a relation has several candidate keys, one is
chosen arbitrarily to be the primary key. The primary key attributes are underlined.
![Page 23: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/23.jpg)
Relational Integrity Constraints
Constraints are conditions that must hold on all valid relation states.
There are three main types of constraints in the relational model: Entity integrity constraints Referential integrity constraints User-define Constraints Users define the constrains themselves
Another implicit constraint is the domain constraint Every value in a tuple must be from the domain of its attribu
te (or it could be null, if allowed for that attribute)
![Page 24: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/24.jpg)
Chapter 5-24
Entity Integrity
Entity Integrity: The primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(R)and have different values. This is because primary key values are used to identify the individual tuples.
![Page 25: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/25.jpg)
Entity integrity
i.e : student ( stu_num , name , gender , age,major_num )
course ( course_num , curse_name , teacher )
select_course ( stu_num , course_num , grades )
![Page 26: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/26.jpg)
Chapter 5-26
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].
![Page 27: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/27.jpg)
Chapter 5-27
Referential Integrity Constraint
Statement of the constraintThe value in the foreign key column (or
columns) FK of the the referencing relation R1 can be either: (1) a value of an existing primary key value of the corresponding primary key PK 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.
![Page 28: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/28.jpg)
i.e 1: student ( stu_num , name , gender , age,major_num )
major ( major_num , major_name )
Foreign key Referen
cing relationReferenc
ed relation
student majorMajor_num
![Page 29: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/29.jpg)
Chapter 5-29
Other Types of Constraints
Constraints:- based on application semantics and cannot be
expressed by the model.- E.g., “the max. no. of hours per employee for
all projects he or she works on is 56 hrs per week”
- A constraint specification language may have to be used to express these
- SQL-99 allows triggers and ASSERTIONS to allow for some of these
![Page 30: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/30.jpg)
Chapter 5-30
![Page 31: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/31.jpg)
Chapter 5-31
5.6
![Page 32: Chapter 4 Rational data model. Chapter 5 Rational data model Chapter 4 Rational data model](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649e2d5503460f94b1d026/html5/thumbnails/32.jpg)
Chapter 5-32
5.7