Chapter 5 Notes

There are two kinds of db integrity that must be

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: ty” that must be addressed: Entity Integrity Entity integrity is an integrity rule which states that every table must have a primary key and that the column or columns chosen to be the primary key should be unique and not null. Referential Integrity a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table) Entity Integrity Entity integrity deals with within-entity rules. These rules deal with ranges and the permission of null values in attributes or possibly between records Entity Integrity No component of the primary key of a base relation is allowed to accept NULLS. In a relational database, we never record information about something we cannot identify. No attribute included in composite primary key can be NULL. Can alternate keys have NULLS? Null Foreign Keys Can a foreign key be null? definition - matches primary key or is null Can a composite foreign key have some attributes null? Referential Integrity Referential integrity concerns two or more tables that are related. Example: IF table A contains a foreign key that matches the primary key of table B THEN values of this foreign key either match the value of the primary key for a row in table B or must be null. Enforcing Integrity Not a trivial task! Not all database management systems enable users to “enforce data integrity” during attribute entry or edit sessions. Therefore, the programmer or the Database Administrator must enforce and/or check for “Integrity.” Functions of a Database Management System Data Storage, Retrieval and Update (CRUD) Catalog or Data Dictionary Shared Update Support Backup and Recovery Services Security Services Integrity Services Data Independence - independent from programs Various Data Manipulation Utilities CRUD Four basic functions, for a given entity they should all be performed with few exceptions, in your system: CREATE READ UPDATE DELETE Using SQL­ Structured Query Language SQL is a standard database protocol, adopted by most ‘relational’ databases Provides syntax for data: Definition Retrieval Functions (COUNT, SUM, MIN, MAX, etc) Updates and Deletes SQL Examples CREATE TABLE SALESREP Item definition expression(s) {item, type, (width)} DELETE table WHERE expression Data Retrieval SELECT list FROM table WHERE condition list - a list of items or * for all items WHERE - a logical expression limiting the number of records selected can be combined with Boolean logic: AND, OR, NOT ORDER may be used to format results For example… Principles of Information Systems, Fifth Edition UPDATE tables SET item = expression WHERE expression INSERT INTO table VALUES ….. Database Normalization Normalization: The process of structuring data to minimize duplication and inconsistencies. The process usually involves breaking down a single Table into two or more tables and defining relationships between those tables. Normalization is usually done in stages, with each stage applying more rigorou...
View Full Document

This note was uploaded on 04/04/2014 for the course MIS 351 taught by Professor Lokshina during the Spring '11 term at SUNY Oneonta.

Ask a homework question - tutors are online