Enforcing Constraints within a Database Application

1
Enforcing Constraints within a Database
Application

• Domain constraints
• Uniqueness
• Referential integrity constraints

• Relationship cardinality
• Business rule
• Triggers

2
What is referential integrity?Referential
integrity is a system of rules that Microsoft
Access uses to ensure that relationships between
records in related tables are valid, and that you
don't accidentally delete or change related data
3

• You can't delete a record from a primary table if
  matching records exist in a related table. For
  example, you can't delete an employee record from
  the Employees table if there are orders assigned
  to the employee in the Orders table.
• You can't change a primary key value in the
  primary table, if that record has related
  records. For example, you can't change an
  employee's ID in the Employees table if there are
  orders assigned to that employee in the Orders
  table.

4

• When referential integrity is enforced, you must
  observe the following rules
• You can't enter a value in the foreign key field
  of the related table that doesn't exist in the
  primary key of the primary table. However, you
  can enter a Null value in the foreign key,
  specifying that the records are unrelated.
• For example, you can't have an order that is
  assigned to a customer that doesn't exist, but
  you can have an order that is assigned to no one
  by entering a Null value in the CustomerID field.

5
You can override the restrictions against
deleting or changing related records and still
preserve referential integrity by setting the
Cascade Update Related Fields and CascadeDelete
Related Records check boxes. When the Cascade
Update Related Fields check box is set, changing
a primary key value in the primary table
automatically updates the matching value in all
related records. When the Cascade Delete
Related Records check box is set, deleting a
record in the primary table deletes any related
records in the related table.
6
Database Normalization
7
Why Normalize?

• Flexibility
• Structure supports many ways to look at the data
• Data Integrity
• Modification Anomalies
• Deletion
• Insertion
• Update
• Efficiency
• Eliminate redundant data and save space

8
Normalization Defined

• In relational database design, the process of
  organizing data to minimize duplication.
• Normalization usually involves dividing a
  database into two or more tables and defining
  relationships between the tables.
• The objective is to isolate data so that
  additions, deletions, and modifications of a
  field can be made in just one table and then
  propagated through the rest of the database via
  the defined relationships. - Webopedia,
  http//webopedia.internet.com/TERM/n/normalization
  .html

9

• Normalization is essentially a two step process
  that puts data into tabular form by removing
  repeating groups and then removes duplicated from
  the relational tables
• It is based on the concepts of normal forms.
• A relational table is said to be a particular
  normal form if it satisfied a certain set of
  constraints. There are currently five normal
  forms that have been defined. In this section, we
  will cover the first three normal forms that were
  defined by E. F. Codd.

10
Functional Dependency Notation

• Major is functionally dependent on SID
• SID ? Major
• Grades (SID,ClassNmaae,Grade)
• Grade is functionally dependent on the
  combination of SID and ClassID
• (SID, ClassID) ? Grade

11
Example
STUDENT (sid, name, major, credits, status,
SSN) What are the FDs for this
relation? Answer sid ? name sid ?
major sid ? credits sid ? status sid ?
SSN or write as sid ? name, major, credits,
status, SSN SSN ? sid SSN ? name SSN ?
major SSN ? credits SSN ? status or write
as SSN ? sid, name, major, credits,
status credits ? status
12
Functional Dependency an Example

• EmployeeNumber ? Name
• EmployeeNumber ? Age
• EmployeeNumber ? Sex

13
A Key

• A key is a group of one or more attributes that
  uniquely identifies a tuple

14
A Combination Key

• Sometimes more than one attribute will be
  required to uniquely identify a tuple.
• If a key consists of more than one attribute, it
  is called a combination (or composite) key.

15
Example of a Combination Key
16
Full Functional Dependency (FFD)

• It is applied to tables with composite keys.
• When a primary key is composite, made of two or
  more columns,then the other columns must be
  identified by the entire key.

17
Example
CLASS (sid, course, sname, facid, time, room,
grade) Q1 What are the FDs? Answer sid,course
? sname, facid,time,room,grade sid ? sname
course ? facid, time, room Q2 Is facid FFD on
sid, course? Answer Is sid, course ? facid?
Yes Is facid FD on any subset of sid,course?
Yes, because course ? facid THEN facid is not
FFD on sid,course because facid is FD on
course which is a subset of sid,course. Q3 What
is FFD on sid, course? Answer grade  Q4 Is
sname FFD on sid? Answer Yes, because sid has no
subset. 
18
First Normal Form(1NF)

• To be in First Normal Form (1NF) a relation must
  have only single-valued attributes -- neither
  repeating groups nor arrays are permitted

19

• Redundancy causes what are called update
  anomalies
• INSERT a certain supplier (s5) cannot be added
  until they supplied a part.
• DELETE If a row is deleted,
• then not only the information about quantity
  and part lost but also information about the
  supplier.
• UPDATE If supplier s1 is moved from London to
  New York,then six rows would have to be updated.

20
Second Normal Form (2NF)

• In 1NF and every non-key column is fully
  dependent on the (entire) primary key

21

• FIRST is in 1NF but not in 2NF because status and
  city are functionally dependent upon only on the
  column s of the composite key (s, p).
• s --gt city, status
• city --gt status
• (s,p) --gt qty

22

• To transform FIRST into 2NF we move the columns
  s, status, and city to a new table called
  SECOND. The column s becomes the primary key of
  this new table.

23

• Tables in 2NF but not in 3NF still contain
  modification anomalies. In the example of SECOND,
  they are
• INSERT. The fact that a particular city has a
  certain status (Rome has a status of 50) cannot
  be inserted until there is a supplier in the
  city.
• DELETE. Deleting any row in supplier destroys the
  status information about the city as well as the
  association between supplier and city.

24
Third Normal Form

• A relational table is in third normal form (3NF)
  if it is already in 2NF and every non-key column
  is non transitively dependent upon its primary
  key. In other words, all nonkey attributes are
  functionally dependent only upon the primary key.
  

25

• Table PARTS is already in 3NF.
• The non-key column, qty, is fully dependent upon
  the primary key (s, p).
• SUPPLIER (second) is in 2NF but not in 3NF
  because it contains a transitive dependency. A
  transitive dependency is occurs when a non-key
  column that is a determinant of the primary key
  is the determinate of other columns. The concept
  of a transitive dependency can be illustrated by
  showing the functional dependencies in SUPPLIER
• SUPPLIER.s --gt SUPPLIER.status
• SUPPLIER.s --gt SUPPLIER.city
• SUPPLIER.city --gt SUPPLIER.status
• Note that SUPPLIER.status is determined both by
  the primary key s and the non-key column city.

26

• To transform SUPPLIER into 3NF, we create a new
  table called CITY_STATUS and move the columns
  city and status into it. Status is deleted from
  the original table, city is left behind to serve
  as a foreign key to CITY_STATUS, and the original
  table is renamed to SUPPLIER_CITY to reflect its
  semantic meaning.

27

• PARTS (s, p, qty) Primary Key (s,p) Foreign
  Key (s) references SUPPLIER_CITY.s
• SUPPLIER_CITY(s, city) Primary Key (s)
  Foreign Key (city) references CITY_STATUS.city
• CITY_STATUS (city, status) Primary Key (city)

28
Advantages of Third Normal Form

• it eliminates redundant data which in turn saves
  space and reduces manipulation anomalies. For
  example,
• INSERT. Facts about the status of a
  city..Likewise, facts about new suppliers can be
  added even though they have not yet supplied
  parts. DELETE. Information about parts supplied
  can be deleted without destroying information
  about a supplier or a city.
• UPDATE. Changing the location of a supplier or
  the status of a city requires modifying only one
  row.

29
Unnormalized data Rows of the table may contain
repeating groups
Decompose rows to remove repeating groups,adding
them to newly created tables
First Normal Form Flat tables with no repeating
column groups within the rows
For rows with keys based on more than one
column, verify all data is dependent on the
key.Split if not
Second Normal Form Data in all non-key columns is
fully dependent upon the primary key.
Eliminate any transitive dependencies (a
column which is not a key but refers to
other data items
Third Normal Form All columns are dependent on
the primary key and are independent of each
other
30
When Not to Normalize

• Want to keep tables simple so user can make their
  own queries
• Avoid processing multiple tables
• Archiving Records
• If No need to perform complex queries
• Flatten and store in one or more tables