Loan-nfo-schema.ppt

Chapter 7: Relational Database Design Chapter 7: Relational Database Design First Normal Form Pitfalls in Relational Database Design Functional Dependencies Boyce-Codd Normal Form and Third Normal Form Decomposition Multivalued Dependencies and Fourth Normal Form Overall Database Design Process First Normal Form Domain is atomic if its elements are considered to be indivisible units Examples of non-atomic domains: Set of names, composite attributes Identification numbers like CS101 that can be broken up into parts A relational schema R is in first normal form if the domains of all attributes of R are atomic Non-atomic values complicate storage and encourage redundant (repeated) storage of data E.g. Set of accounts stored with each customer, and set of owners stored with each account We assume all relations are in first normal form (revisit this in Chapter 9 on Object Relational Databases) First Normal Form (Contd.) Atomicity is actually a property of how the elements of the domain are used. E.g. Strings would normally be considered indivisible Suppose that students are given roll numbers which are strings of the form CS0012 or EE1127 If the first two characters are extracted to find the department, the domain of roll numbers is not atomic. Doing so is a bad idea: leads to encoding of information in application program rather than in the database. Pitfalls in Relational Schemas Relational database design requires that we find a “good” collection of relation schemas. A bad design may lead to Repetition of Information. Inability to represent certain information. Design Goals: Avoid redundant data Ensure that relationships among attributes are represented Facilitate the checking of updates for violation of database integrity constraints. Example Consider the relation schema: Lending-schema = (branch-name, branch-city, assets,  customer-name, loan-number, amount) Redundancy: Data for branch-name, branch-city, assets are repeated for each lo