DATABASE LOGICAL DESIGN -- II

1. DATABASE LOGICAL DESIGN -- II Chandra S. Amaravadi

2. DESIGN CONCEPTS NORMAL FORMS REPEATING GROUPS PARTIAL DEPENDENCIES TRANSITIVE DEPENDENCIES MULTI-VALUED DEPENDENCIES

3. NORMALIZATION unnormalized Normalization: The process of removing unwanted functional dependencies. Also: The process of designing well-structured tables. Normal Forms: Normal forms are stages in normalization Remove R.G. 1st NF Remove P.D. 2nd NF Remove T.D. 3rd NF Remove other anomalies BCNF Remove M.V.D 4th NF

4. NORMAL FORMS Normal forms are stages in the normalization of a table Consider only first three during design • 1st NF -- No repeating groups • 2ndNF -- No partial dependencies (& no r.g.) • 3rdNF -- No transitive dependencies (& no r.g., no p.d.) • 4thNF -- No Multi-valued dependencies (no r.g., no p.d., no t.d.) BOYCE CODD NORMAL FORM (BCNF) -- Each and every determinant is a candidate key

5. DESIGN CONCEPT Repeating group: A table with more than one set of values at row and column intersection. FLIGHTS Repeating groups cause anomalies and must be removed

6. DOES THIS HAVE R.G.? FLIGHTS

7. NORMALIZED TABLES FLIGHTS FARES Repeating groups are dealt with by removing the r.g. along with primary key and putting it in a separate table.

8. DESIGN CONCEPTS.. Partial dependency : An attribute (a non-key attribute) is dependent on part of the pkey. Example: Suppose a & b form the primary key and c depends only on a a, b c c depends only on a a, b c ideal case

9. PARTIAL DEPENDENCY.. Example: Emp_id and course_title is the primary key, and salary and Date course was taken are attributes, Emp_id, Course_title Salary , date taken Remove the p.d. and store it along with its determinant

10. PARTIAL DEPENDENCY.. EMPLOYEES

11. NORMALIZED TABLES EMPLOYEE EMP COURSE

12. DESIGN CONCEPTS.. Transitive dependency : An attribute is dependent on a non-key attribute In A, a1 is pkey and a2 --> a3 a1 a2 a3 in this case a3 ideal case a1 a2 Remove the t.d. and store it along with its determinant

13. TRANSITIVE DEPENDENCY.. Example: Flt# is the primary key, in a relation that consists of Flt#, From, To, and Distance flt#, origin destination distance FLIGHTS

14. TRANSITIVE DEPENDENCY.. Example: isbn# is the primary key, in a relation that consists of title, pubcode and pubname. isbn#, title pubcode pubname BOOKS

15. DESIGN CONCEPTS.. Multi-valued dependency: If there are three or more attributes (‘a’, ‘b’, ‘c’) in a relation such that for each “a” there are multiple values of “b” and for each “a” there are multiple values of “c” and ‘b’ and ‘c’ are independent then a MVD occurs • a relation consists of three or more attributes (a, b, c..) • b and c are multi-valued • non-key attributes are independent of one another In A, a -->> b, a -->> c b & c are independent

16. MULTI-VALUED DEPENDENCIES.. Example: Flt# is the primary key, in a relation that consists of Flt#, Fare, Meals Fares (flt#, fare) Meals (flt#, meals) Flt#, Fare Meals Student (SID#, sgpa) Course (SID#, c#) SID#, SGPA C# Is this Correct?

17. FOR DISCUSSION Identify problems (if any) with the following tables and their normal forms Orders (ord#, p#, ord_dt, ord_amt) Order(ord#, orddt., qty) Order(ord#, orddt., p#) Parts(p#, #finished, c#, die#) Ord_for_parts (ord#, p#, descr, qty) Die(die#, shelf#, p#) Die(die#, shelf#) ord#: Unique identifier for order (an order can be for many parts) ord dt: date of order p#: unique number for parts qty: # of a certain part that is ordered c# : container for finished parts (one container per part) #fin: # of finished parts die#: Die used to manufacture parts (one die per part) shelf#: the shelf where the die is located

18. THE DESIGN PROCESS

19. THE DESIGN PROCESS.. THE CONVENTIONAL APPROACH • Start with the relation that has all the attributes • Identify repeating groups • Remove repeating groups along with their candidate key • Identify partial dependencies • Remove the dependency along with its candidate key • Identify transitive dependencies • Remove transitive dependency along with its candidate key You could alternatively use the FD approach to design

20. DESIGN EXAMPLES Employee Equipment

21. Instructor-Office Assume each course has one instructor

22. DESIGN EXAMPLES .. Employee

23. DESIGN EXAMPLES.. DRIVER NAME: Unser DRIVER NO.: 154 11/09/XX DATE: CAR CLASS: Sprint

24. DESIGN EXAMPLE.. ASSUMPTIONS: • Each driver drives one car class. • There can be many races on a given date. • Each race is given a number valid for that date. • Each car has one owner. • A driver can drive for other owners in different races. • The database can be used to print similar reports on other drivers.

25. THIS SLIDE HAS BEEN LEFT BLANK

26. DISCUSSION QUESTIONS • Why is database design necessary? • Can we carry out design without doing Normalization? • What is a Functional Dependency? Can there be a functional dependency between two records? • What are anomalies? Are they desirable? • Are anomalies removed after design? • Are anomalies related to the degree of structure of a table? • Can there be a partial dependency with a pkey that is simple? • Can there be transitive dependency between the pkey and another attr?