ch7-2 - Chapter 7: Relational Database Design Chapter 7:...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Chapter 7: Relational Database Design Chapter 7: Relational Database Design ©Silberschatz, Korth and Sudarshan 7.2 Database System Concepts Chapter 7: Relational Database Design Chapter 7: Relational Database Design ! First Normal Form ! Pitfalls in Relational Database Design ! Functional Dependencies ! Decomposition ! Boyce-Codd Normal Form ! Third Normal Form ! Multivalued Dependencies and Fourth Normal Form ! Overall Database Design Process
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 ©Silberschatz, Korth and Sudarshan 7.3 Database System Concepts 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) ©Silberschatz, Korth and Sudarshan 7.4 Database System Concepts 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.
Background image of page 2
3 ©Silberschatz, Korth and Sudarshan 7.5 Database System Concepts Pitfalls in Relational Database Design Pitfalls in Relational Database Design ! 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. ©Silberschatz, Korth and Sudarshan 7.6 Database System Concepts Example 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 loan that a branch makes " Wastes space " Complicates updating, introducing possibility of inconsistency of assets value ! Null values " Cannot store information about a branch if no loans exist " Can use null values, but they are difficult to handle.
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
4 ©Silberschatz, Korth and Sudarshan 7.7 Database System Concepts Decomposition Decomposition ! Decompose the relation schema Lending-schema into: Branch-schema = (branch-name, branch-city,assets) Loan-info-schema = (customer-name, loan-number, branch-name, amount) !
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 46

ch7-2 - Chapter 7: Relational Database Design Chapter 7:...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online