Transcript What is a Database Management System?

Relational Model
Chapter 7
Database Management
COP4540, SCS, FIU
Relational Model Concepts
• The relational model represents database as
a collection of relations.
• Informally, each relation looks like a table.
• Each row in the table represents an entity
instance or a relationship instance.
Database Management
COP4540, SCS, FIU
Relational Definitions
• Relation
–
–
–
–
–
–
Every relation has a unique name.
Every attribute value is atomic.
Every row is unique.
Attributes in tables have unique names.
The order of the rows is irrelevant.
The order of the columns is irrelevant. (as long
as the correspondence between attributes and
values is maintained.
Database Management
COP4540, SCS, FIU
Example of relation and table
Id
Name
Dome Address
Type Tower
APT
01
Mary
02
Smith
Id
Name DomeType Tower APT
01
02
Mary
Smith
RSQ
D
GA
SSQ
6
2F
RSQ
SSQ
D
6
GA
2F
Database Management
COP4540, SCS, FIU
Relational Definitions (2)
• Domain
– A domain D is a set of atomic values.
– A domain is usually given a name, data type,
and format.
– Each attributes may have its own domain.
– It is possible for several attributes to have the
same domain but with different roles.
– Examples (Page 197)
Database Management
COP4540, SCS, FIU
Relational Definitions (3)
• Tuple: a record in a relation ( a row in the
table)
• Attribute: a column in a relation.
• View: a collection of attributes from several
relations. (Actually a kind of query to
database).
• NULL values
– The values of some attributes with a particular
tuple may be unknown.
Database Management
COP4540, SCS, FIU
Terms and Notations
• A relation schema R
– denoted by R(A1, A2, …, An), is made up of a relation
name R and a list of attributes A1, A2, …, An.
– The domain of attribute Ai is denoted by dom(Ai)
– The degree of a relation is the number of attributes n
of its relation schema.
• A relation (or relation state) r of the relation
schema R(A1, A2, …, An), also denoted by r(R), is
a set of n-tuples r ={t1, t2, …, tm}
where t = <v1, v2, …, vn>
Database Management
COP4540, SCS, FIU
Terms and Notations (2)
• An n-tuple t in a relation r(R) is denoted by
t = <v1, v2, …, vn>, where vi is the value
corresponding to attribute Ai.
– Both t[Ai] and t.Ai refer to the value vi in t for
attribute Ai
– Both t[Au, Av, …, Az] and t.(Au, Av, …, Az) refer
to the subtuple of values <vu, vv, …, vz> from t
corresponding to the attributes specified in the
list.
Database Management
COP4540, SCS, FIU
Terms and Notations (3)
•
•
•
•
The letters Q, R, S denote relation names.
The letters q, r, s denote relation states.
The letter t, u, v denote tuples.
In general, the name of relation schema also
indicates the current set of tuples in that relation.
• An attribute A can be qualified with the relation R
to which it belongs by R.A because all attributes
names in a particular relation must be distinct.
Database Management
COP4540, SCS, FIU
Domain Constraints
• Allowable values for an attribute.
–
–
–
–
Atomic
Data type
Subrange
Enumerated type.
Database Management
COP4540, SCS, FIU
Keys in a Relation
• Each relation has at least one KEY.
• A KEY uniquely identifies a tuple.
• A KEY can be one attribute or a combination of
multiple attributes.
• Primary Key & Candidate Key.
• Indicate a key by underlining the key attributes.
• Entity Integrity
– No primary key attribute may be null.
Database Management
COP4540, SCS, FIU
Referential Integrity & Foreign keys
• Foreign key
A set of attributes FK in relation R1 is a foreign key of R1 that
references R2 if it satisfies the following two rules:
1. The attributes in FK have the same domain(s) as the primary
key attribute PK of R2
2. A value of FK in a tuple t1 of the current state r1(R1) either
occurs as a value of PK for some tuple t2 in the current state r2(R2)
(in this case we have t1[FK] = t2[PK]) or is null.
Note that a foreign key can refer to its own relation.
• Referential Integrity
– A rule that states that either a foreign key value must match a
primary key value in the other relation or else the foreign key value
must be null.
Database Management
COP4540, SCS, FIU
Examples of Foreign keys
EMPLOYEE SSN Name BDate Salary DeptNo SuperSSN
DEPARTMENT
Dname Dnum Manger
EMPLOYEE(SSN, Name, Bdate, Salary, DeptNo, SuperSSN)
DEPARTMENT(Dname, Dnum, Manager)
Database Management
COP4540, SCS, FIU
Well-Structured Relations
PROF(ID, COURSE_Title, Salary, Date)
• Insertion Anomaly: Insertion of a new
record encounters unreasonable restrictions.
– E.g. The example relation requires the
knowledge of course data in order to enter a
new professor. This is, of course, not a good
design of relations.
Database Management
COP4540, SCS, FIU
Well-Structured Relations (2)
PROF(ID, COURSE_ID, Text, Salary, Date)
• Deletion Anomaly: Loss of information due
to deletion.
– E.g. The example relation can lead to loss of
information on the text of courses when a
professor is deleted.
Database Management
COP4540, SCS, FIU
Well-Structured Relations (3)
PROF(ID, COURSE_ID, Text, Salary, Date)
• Modification Anomaly: Change of data
contents may lead to inconsistent data.
– E.g. When a professor is given a raise, all rows
pertaining to the professor must be modified;
otherwise, the same professor will have
different salaries.
Database Management
COP4540, SCS, FIU
Representing attributes in ODL
• A direct conversion of classes in ODL into relations
– All properties of the class are attributes.
– The type of attributes are atomic.
• What about non-atomic attributes?
– Problem: ODL allows attribute types build from
structures and collection types.
– Structure: make one sub-attribute for each field.
– Set: make one tuple for each member of the set.
Database Management
COP4540, SCS, FIU
Example
class Drinkers (key name) {
attribute string name;
attribute Struct Addr
{string street, string city, int zip} address;
attribute Set<string> phone;
};
Name Street City Zip Phone
n1
s1
c1
z1 p1
n1
s1
c1
z1 p2
Note that the key for the class (name) is not the key for the relation.
Name in the class determines a unique object, including a unique set of phones.
Name in the relation does not determine a unique tuple.
Tuples are not identical to objects.
Database Management
COP4540, SCS, FIU
Decompose Relations?
One option is to get phone into a separate relation
(with name). The database would look like:
Name Street City Zip
n1
s1
c1
z1
Name Phone
n1
p1
n1
p2
Advantages:
1. Avoids redundancy in address components.
2. Handles the case where someone has no phone.
Disadvantage:
Harder to answer queries that jump between two
relations, e.g., “what city is phone 650-725-4802?”
Database Management
COP4540, SCS, FIU
A Design Problem
class Family {
attribute Set<string> parents;
attribute Set<string> children;
};
• 1. What is the key?
• 2. How should we represent a family with two parents and
three children?
• 3. What if we use “Array<string>” (Suppose the size of
the array is 2) to define “parents” ?
Database Management
COP4540, SCS, FIU
Representing Single-valued Relationship
• Single-valued relationship
– Do not use collection type when define relationship.
– Actually one-one or many-one relationship.
• For a single-valued relationship R from classes C1
to class C2,
1. Create two relations T1 and T2 for the classes C1 and
C2 respectively.
2. Add the key attributes of T2 into T1.
3. What if there is no KEY?
Database Management
COP4540, SCS, FIU
Representing Multi-valued Relationship
• Single-valued relationship
– Use collection type when define relationship.
– Actually one-many or many-many relationship.
• For a single-valued relationship R from
classes C1 to class C2,
1. Create two relations T1 and T2 for the classes
C1 and C2 respectively.
2. Add the key attributes of T2 into T1.
3. More redundancy occurs.
Database Management
COP4540, SCS, FIU
Representing Relationship in One Direction
• For one-one relationship and many-many
relationships, we can chose to express the either
direction.
• For one-many and many-one relationships, we
should chose to represent the many-one
relationship since it comes with less
redundancy.
Database Management
COP4540, SCS, FIU