This preview shows page 1. Sign up to view the full content.
Unformatted text preview: blem is referred to as an insert anomaly.
13-39 Last Name First
Name 333-33-3333 Simpson Alice 333-3333 ACCT-3603 1M 9:00 AM 333-33-3333 Simpson Alice 333-3333 FIN-3213 3 Th 11:00 AM 333-33-3333 Simpson Alice 333-3333 MGMT-3021 11 Th 12:00 PM 111-11-1111 Sanders Ned 444-4444 ACCT-3433 2T 10:00 AM 111-11-1111 Sanders Ned 444-4444 MGMT-3021 5W 8:00 AM 111-11-1111 Sanders Ned 444-4444 ANSI-1422 7F 9:00 AM 123-45-6789 Moore Artie 555-5555 ACCT-3433 2T 10:00 AM 123-45-6789 Moore Artie 555-5555 FIN-3213 3 Th 11:00 AM Student ID Phone No. Course No. Section Day Time • If Ned withdraws from all his classes and you eliminate all three of his
rows from the table, then you will no longer have a record of Ned. If Ned
is planning to take classes next semester, then you probably didn ’t really
want to delete all records of him.
• This problem is referred to as a delete anomaly.
13-40 RELATIONAL DATABASES Basic requirements of a relational database Every In
In column in a row must be single valued. other words, every cell can have one and only one
13-41 RELATIONAL DATABASES Basic The requirements of a relational database
primary key cannot be null. The
The primary key uniquely identifies a specific row in
the table, so it cannot be null, and it must be unique for
every This rule is referred to as the entity integrity rule.
13-42 RELATIONAL DATABASES Basic requirements of a relational database A foreign key must either be null or correspond
to the value of a primary key in another table.
This rule is referred to as the referential integrity rule.
referential The rule is necessary because foreign keys are used to
link rows in one table to rows in another table.
Student ID Last Name First Name Phone No. Advisor No. 333-33-3333 Simpson Alice 333-3333 1418 111-11-1111 Sanders Ned 444-4444 1418 123-45-6789 Moore Artie 555-5555 1503 ADVISORS
Advisor No. Last Name First Name Office No. 1418 Howard Glen 420 1419 Melton Amy 316 1503 Zhang Xi 202 1506 Radowski J.D. 203 Advisor No. is a foreign key in the STUDENTS table. Every
incident of Advisor No. in the STUDENTS table either matches
an instance of the primary key in the ADVISORS table or is null.
13-44 RELATIONAL DATABASES The
The preceding three constraints produce a
well-structured (normalized) database in
which: Data are consistent. Redundancy is minimized and controlled. In
In a normalized database, attributes appear
multiple times only when they function as
foreign The referential integrity rule ensures there
will be no update anomaly problem with
Chapter foreign keys.
13-45 RELATIONAL DATABASES Normalization Starts
Starts with the assumption that everything is
initially stored in one large table.
initially A set of rules is followed to decompose that
initial table into a set of normalized tables.
initial Objective is to produce a set of tables in thirdnormal form (3NF) because such tables are free
of update, insert, and delete anomalies.
View Full Document
- Spring '12