Chapter51 - CSIS0278A Introduction to Database Management...

Info icon This preview shows pages 1–10. Sign up to view the full content.

View Full Document Right Arrow Icon
Click to edit Master subtitle style CSIS0278A Introduction to Database Management Systems Lecture 5 Database Design Part 1: Functional Dependency Dr. Reynold Cheng Based on the Ch. 7 notes of “Database System Concepts” by A. Silberschatz, H. Korth & S. Sudarshan and notes by Dr. Ho Wai Shing
Image of page 1

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

View Full Document Right Arrow Icon
2DB060:2 Overview l First Normal Form l Pitfalls in Relational Database Design l Functional Dependencies l Lossless-Join Decomposition l Boyce-Codd Normal Form
Image of page 2
3DB060:3 Multi-Valued Attributes l Suppose in the ER diagram, phone is a multi- valued attribute of an employee empID l Possible representation: name empI D addr phone deptI D deptNam e John John 1 1 Pokfulam Pokfulam 123456 78 234567 89 1 1 Sales Sales John 1 Pokfulam 965321 46 2 HR Peter 2 Central 864592 13 2 HR Mary 3 Mei Foo 666356 66 1 Sales
Image of page 3

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

View Full Document Right Arrow Icon
4DB060:4 First Normal Form l A domain is atomic if its elements are indivisible. l Multi-valued/composite attributes are not atomic . l A relational schema R is in First Normal Form (1NF) if the domains of all attributes in R are atomic. l We assume all relations are in 1NF. l However, object-oriented databases often support the storage of non-atomic attributes.
Image of page 4
5DB060:5 Problem of Handling Non-atomic Values l Handling multi-valued attributes in 1NF may encourage redundant data storage l How to solve this systematically? name empI D addr phone deptI D deptNam e John John 1 1 Pokfulam Pokfulam 123456 78 234567 89 1 1 Sales Sales John 1 Pokfulam 965321 46 2 HR Peter 2 Central 864592 13 2 HR Mary 3 Mei Foo 666356 66 1 Sales
Image of page 5

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

View Full Document Right Arrow Icon
6DB060:6 Overview l First Normal Form l Pitfalls in Relational Database Design l Functional Dependencies l Lossless-Join Decomposition l Boyce-Codd Normal Form
Image of page 6
7DB060:7 Relational Database Design Goals l Avoid redundant data ( Efficient ) l All relationships among attributes are represented ( Correct ) l Facilitates checking of updates for violation of integrity constraints. ( Verifiability ) l Allows applications to be developed easily ( Application-friendly )
Image of page 7

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

View Full Document Right Arrow Icon
8DB060:8 Pitfalls in Relational Database Design l Relational database design concerns the finding of a “good” collection of relation schemas. l A “bad” design involves: l Too many relations – requires too many joins to find the required information l Too few relations – may lead to repeated/ duplicated information (e.g., P.5)
Image of page 8
9DB060:9 Example l Lending(b_name, b_city, assets, c_name, l_num, amount) l Redundancy : b_name, b_city, assets are repeated for each loan that a branch makes.
Image of page 9

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

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

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern