database systems lecture # 8 11 th feb,2011. the relational model of data the term relation is...
TRANSCRIPT
![Page 1: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/1.jpg)
Database Systems
Lecture # 8
11th Feb,2011
![Page 2: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/2.jpg)
The Relational Model of Data
The term relation is basically just a mathematical term for a table.
DBMS products that are based on the relational model of data have come to dominate the database marketplace.
The introduction of the relational model by E. F. Codd in 1969-70 was the most important event in the entire history of the database field.
![Page 3: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/3.jpg)
The Relational Model - continued
C. J. Date was the first to recognize the significance of the relational model and has been the most recognized author, lecturer, researcher, and consultant of the relational technology
He is best known for his book “An Introduction to Database Systems (seventh edition published in 2000),” which has sold nearly 650,000 copies and is used by several hundred colleges and universities worldwide.
![Page 4: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/4.jpg)
Terminology
S#:S# SNAME:NAME STATUS:STATUS CITY:CITY
S1S2S3S4S5
SmithJonesBlakeClarkAdams
2010302030
LondonParisParisLondonAthens
Rel
atio
n
Tup
le
Attributes
Primary Key
London, Tucson, Paris, ...
S# NAME STATUS CITY
Dom
ains
![Page 5: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/5.jpg)
Relational Data Objects: Domains and Relations
A relation is table A tuple corresponds to a row of such a table
and attribute to a column The number of tuples is called the
cardinality and number of attributes are called degree
![Page 6: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/6.jpg)
Domain
A domain is a pool of values, from which specific attributes of specific relations draw their actual values.
Synonym of data type (Type for short). Can be system-defined, e.g., text, number, currency,
yes/no, date/time, hyperlink, autonumber. Or user-defined, e.g., POINT.
For example, the domain labeled S# is the set of all legal supplier numbers, and the set of S# values appearing in relation S at any given time is constrained to be some subset of that set.
![Page 7: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/7.jpg)
Terminologies Used:
Formal Informal Relation table Tuple row or record Cardinality number of rows Attribute column or field Degree number of
columns Domain pool of legal
values
![Page 8: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/8.jpg)
Tables Relational database is a collection of tables Heading: table name and column names Body: rows, occurrences of data
StdSSN StdLastName StdMajor StdClass StdGPA 123-45-6789 WELLS IS FR 3.00 124-56-7890 NORBERT FIN JR 2.70 234-56-7890 KENDALL ACCT JR 3.50
Student
![Page 9: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/9.jpg)
Integrity
The term integrity refers to the accuracy and correctness of data in the database.
The DBMS should automatically enforce specified integrity constraints. Entity integrity: primary keys
Each table has column(s) with unique values Ensures entities are traceable
Referential integrity: foreign keys Values of a column in one table match values from a
source table Ensures valid references among tables
![Page 10: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/10.jpg)
Integrity (Fig. from C.J. Date)
S# SNAME STATUS CITY
S1S2S3S4S5
SmithJonesBlakeClarkAdams
2010302030
LondonParisParisLondonAthens
P# PNAME COLOR WEIGHTP1P2P3P4P5P6
NutBoltScrewScrewCamCog
RedGreenBlueRedBlueRed
121717141219
CITYLondonParisRomeLondonParisLondon
S#
S1S1S1S1S1S1S2S2S3S4S4S4
P#
P1P2P3P4P5P6P1P2P2P2P4P5
QTY
300200400200100100300400200200300400
S
P
SP
![Page 11: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/11.jpg)
Integrity (…Cont) The term integrity refers to the accuracy or correctness of data in
the database.
Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency.
A given database might be subject to any number of integrity constraints of arbitrary complexity.
The DBMS needs to be informed of such constraints, needs to enforce them somehow.
Basically by rejecting any update that would otherwise violate them.
![Page 12: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/12.jpg)
Integrity (…Cont)
When a new constrain is declared,
The system must first make sure the database currently satisfies it;
If not, the new constraint is rejected;
Otherwise it is accepted and enforced from that point forward.
We can get rid of existing constraints.
![Page 13: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/13.jpg)
Types of Constraints
Type (domain) Constraint: specifies the legal values for a given type.
Attribute constraint: specifies the legal values for a given attribute.
Table (relation) constraint: specifies the legal values for a given table. Multiple attributes may be involved.
Database constraint: specifies the legal values for a given database. Multiple tables may be involved.
![Page 14: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/14.jpg)
Type or Domain Constraints
Type constraints are the most elementary form of integrity constraint.
A type constraints is an enumeration of the legal value of the type, a specification of the values that make up the type in question.
Type constraints are checked immediately.
![Page 15: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/15.jpg)
Type or Domain Constraints (Cont.)
The check clause in SQL permits domains to be restricted: Use check clause to ensure that an hourly-wage
domain allows only values greater than a specified value.
create domain hourly-wage numeric(5,2)constraint value-test check(value > = 4.00)
The domain hourly-wage is declared to be a decimal number with 5 digits, 2 of which are after the decimal point
The domain has a constraint that ensures that the hourly-wage is greater than 4.00
![Page 16: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/16.jpg)
Attribute Constraints
An attribute constraint is just a declaration to the effect that a specified attribute is of a specified type.
For example: create table account
(branch-name char(15),account-number char(10) not null,balance integer,……)
Attribute constraints are part of the definition of the attribute.
Any attempt to introduce an attribute value into the database that is not a type of the relevant type will simply rejected. Such a situation should never arise.
![Page 17: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/17.jpg)
Table Constraint
A table constraint is a constraint on an individual table.
Example: Suppliers in London must have status 20. Two attributes, CITY and STATUS, of table S are
involved.
![Page 18: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/18.jpg)
Database Constraint
A database constraint is a constraint that interrelates two or more distinct tables.
Example: No suppliers with status less than 20 can supply any
part in a quantity greater than 500. Two tables, S and SP, are involved.
![Page 19: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/19.jpg)
Reference:
Chapter No 4 of the book by (C. J. Date) Home work:
Read the chapter 4 of the book
![Page 20: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/20.jpg)
Keys
The concept of Keys is very important to the relational model.
We'll discuss the following types of keys. Candidate Keys
Primary Keys and Alternative Keys Foreign Keys
![Page 21: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/21.jpg)
Formal Definitions
Candidate key: minimal number of attributes with unique values.
Null value: special value meaning value unknown or inapplicable
Primary key: a designated candidate key; cannot contain null values
Foreign key: column(s) whose values must match the values in a candidate key of another table
![Page 22: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/22.jpg)
Candidate Keys Let R be a table. By definition, at any given time,
no two tuples in the value of R are duplicates of one another. (Remember Entity Integrity)
Let K be a set of attributes of R. Then K is a candidate key for R if and only if it satisfies:
(1) Uniqueness: No legal value of R ever contains two distinct tuples with the same value for K.
(2) Irreducibility (or minimality): No proper subset of K has the uniqueness property.
![Page 23: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/23.jpg)
Candidate Key Examples
For S {S#, SNAME, STATUS, CITY}, S# and SNAME do not have duplicates, so Candidate KEY {S#} Candidate KEY {SNAME}
For SP {S#, P#, QTY}, {S#, P#} does not have duplicates, so Candidate KEY {S#, P#}
![Page 24: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/24.jpg)
Primary and Alternative Keys
Exactly one candidate key must be chosen as the primary key, and the others are alternative keys.
Primary key and alternative keys are both candidate keys.
For S {S#, SNAME, STATUS, CITY}, if we choose KEY {S#} as the primary key, then KEY {SNAME} is an alternative key.
For SP {S#, P#, QTY}, We must choose KEY {S#, P#} as the primary key, and
there will be no alternative key.
![Page 25: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/25.jpg)
Foreign Keys
A foreign key is a set of attributes of one table R2 whose values are required to match values of some candidate key of some table R1.
{S#} is a foreign key of SP that references the primary key {S#} of S.
Any S# value of SP must exist in S. Similarly, {P#} is another foreign key of SP that
references the primary key {P#} of P.
![Page 26: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/26.jpg)
Integrity Rules
Entity integrity No two rows with the same primary key value No null values in a primary key
Referential integrity Foreign keys must match candidate key of source table Foreign keys in some cases can be null The database must not contain any unmatched foreign key
values. If B references A, A must exist.
![Page 27: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/27.jpg)
Referenced Rows Referenced row
Foreign keys reference rows in the associated primary key table
Each supply row references supplier and part rows.
Actions on referenced rows Delete a referenced row Change the primary key of a referenced row Referential integrity should not be violated
![Page 28: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/28.jpg)
Possible Actions
Restrict: do not permit action on the referenced row
Cascade: perform action on related rows Nullify: only valid if foreign keys accept null
values Default: set foreign keys to a default value
![Page 29: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/29.jpg)
Referential Actions on Foreign Keys
When we delete a tuple from a table, say S, that is referenced by another table, say SP,
if the primary key {S#} value of S exists in SP, there are several choices of referential actions:
ON DELETE CASCADE The corresponding tuples in SP will be deleted too.
ON DELETE RESTRICT The deletion of the tuple from S is rejected.
![Page 30: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/30.jpg)
Referential Actions (Cont.)
When we update a tuple from a table, say S, that is referenced by another table, say SP,
There are similar choices of referential actions: ON UPDATE CASCADE ON UPDATE RESTRICT
There could be other choices besides these three. , e.g., ON UPDATE SET DEFAULT. ON UPDATE SET NULL.
![Page 31: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/31.jpg)
Integrity (Fig. from C.J. Date)
S# SNAME STATUS CITY
S1S2S3S4S5
SmithJonesBlakeClarkAdams
2010302030
LondonParisParisLondonAthens
P# PNAME COLOR WEIGHTP1P2P3P4P5P6
NutBoltScrewScrewCamCog
RedGreenBlueRedBlueRed
121717141219
CITYLondonParisRomeLondonParisLondon
S#
S1S1S1S1S1S1S2S2S3S4S4S4
P#
P1P2P3P4P5P6P1P2P2P2P4P5
QTY
300200400200100100300400200200300400
S
P
SP
![Page 32: Database Systems Lecture # 8 11 th Feb,2011. The Relational Model of Data The term relation is basically just a mathematical term for a table. DBMS products](https://reader035.vdocuments.us/reader035/viewer/2022081603/56649e0e5503460f94af8bf0/html5/thumbnails/32.jpg)
SQL Syntax for Actions (Just a flavor)
CREATE TABLE Enrollment( OfferNo INTEGER NOT NULL, StdSSN CHAR(11) NOT NULL,
EnrGrade DECIMAL(3,2),
CONSTRAINT PKEnrollment PRIMARY KEY(OfferNo, StdSSN),
CONSTRAINT FKOfferNo FOREIGN KEY (OfferNo) REFERENCES Offering
ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FKStdSSN FOREIGN KEY (StdSSN) REFERENCES Student
ON DELETE RESTRICT ON UPDATE CASCADE )