1 / 41

DATABASE LOGICAL DESIGN - I

DATABASE LOGICAL DESIGN - I. Chandra S. Amaravadi. DATABASE DESIGN. The objective of database design is to develop a set of well structured tables so that:. Data is in the most efficient form No uncontrolled redundancies Queries/reporting facilitated

hilda-whitehead
Télécharger la présentation

DATABASE LOGICAL DESIGN - I

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DATABASE LOGICAL DESIGN - I Chandra S. Amaravadi

  2. DATABASE DESIGN The objective of database design is to develop a set of well structured tables so that: • Data is in the most efficient form • No uncontrolled redundancies • Queries/reporting facilitated • Database can be easily implemented The output of design are a set of well structured/normalized tables.

  3. ILL STRUCTURED TABLES EXAMPLE OF EMPLOYEE Ill structured tables are problematic. why?

  4. DESIGN CONCEPTS ANOMALY: An inconsistency in the database that results from either adding records, deleting them or updating them. Three types of anomalies: • Insertion • Deletion • Update

  5. DESIGN CONCEPTS.. INSERTION ANOMALY: An insertion anomaly occurs if when trying to add a record, it cannot be added without additional information or it may need to be added in multiple places instead of in one location. DELETION ANOMALY: A deletion anomaly occurs, if when we try to delete a record, we have to perform the deletion a number of times, or if we lose information we did not intend to lose.

  6. DESIGN CONCEPTS.. UPDATE ANOMALY/MODIFICATION ANOMALY: An update anomaly occurs, if when we try to update a record, instead of making the update in one location, we need to update in multiple locations. Are anomalies common in the file processing approach?

  7. ILLUSTRATION OF ANOMALIES EMPLOYEES

  8. ANOMALIES.. 1. Insert Tom White, ID 130, Finance, 60K 2. Delete Employee with ID 140 3. Employee with ID 100 gets a 10% salary increase

  9. WELL STRUCTURED TABLE A Well structured table may be defined in a number of ways: • no repeating groups • redundancies minimized • anomalies minimized • all attributes dependent on pkey • full functional dependency 9

  10. METHODS OF DESIGN Design can be carried out with: • ER diagrams (using thumb rules) • Normalization theory • FD approach • brute force

  11. DESIGN FROM ER CHARTS, USING THUMB RULES

  12. DESIGN FROM ER: Following are the rules for converting an ER diagram into a design 1. In the case of 1:1, put each eclass into a separate table, with a cross-reference key in either. 2. In the case of 1:M, put each eclass into separate tables; Include the Pkey from the one side as a foreign key on the M side. 3. In the case of M:N, put each eclass into a separate table; put the relationship itself into a third table with Pkey consisting of Pkeys from the M and N sides.

  13. DESIGN FROM ER.. PRODUCT HAS WARRANTY Prod# Descr War# Eff_Dt Products Warranty Prod# Descr ??? War# Eff_dt ??? In the case of 1:1, put each eclass into a separate table and ____________________.

  14. DESIGN FROM ER.. Cust# Name Customer Cust# Name CUSTOMER Orders Places Ord# Ord_dt ???? ORDERS Are for PRODUCTS Ord# Ord_dt In the case of 1:M, put each eclass into a separate table and ___________.

  15. DESIGN FROM ER.. Qty ORDERS Are for PRODUCTS Ord# Ord_dt Prod# Descr. Orders Products ord# ord_dt prod# descr Orders for Products ??????? qty In the case of M:N, put each _____ into a separate table and place the ___________ into a separate table with keys from ___ side and __ side

  16. DISCUSSION DEVELOP DESIGNS FOR THE FOLLOWING SITUATIONS SS# name proj# mgr s# location. DRIVER PROJECT SUPPLIER assigned has supplies CAR EMPLOYEES COMPANY car# model SS# name title addr.

  17. THREE WAY AND HIGHER d# name How can we do design with degree >= 3? DEPT proj# HAS PROJECTS cost EMPLOYEES emp# ti

  18. DESIGN WITH NORMALIZATION

  19. DESIGN USING NORMALIZATION Normalization: The process of designing well-structured tables. Use normalization theory if: • Data relationships complex • No planning/ER done • Maintenance • Alternative to ER approach

  20. DESIGN CONCEPTS FUNCTIONAL DEPENDENCIES

  21. DESIGN CONCEPTS.. Functional dependency: A relationship between two or more attributes such that if we know one attribute we can uniquely determine other attributes. a --> b, c.. ; “a determines b, c..”, ; “b,c.. are dependent on a” FD test: For each value of a there is one and only one value of b.

  22. DESIGN CONCEPTS.. Functional dependency (FD): • relationship between attributes (L -> R unless specified) • a à b, c, d is referred to as a functional dependency diagram • Each value of a is associated with one value of b; one c… • For a given value of “b” (or “c”..) there can be many values of “a” a b : a determines b, b is dependent on a a b : a does not determine b, no relationship (normally omitted) a b : there are multiple values of b for each a.

  23. FD DIAGRAM EXAMPLES pp# name of issuing country flt# name of captain student id GPA player team name pp# visa#’s GPA student id prod descr. prod price price descr. gpa descr. Please note that FD is valid only in the first group; second group indicates need for additional keys (may result in partial functional dependencies)

  24. FULL FD RULE relational Database Rule (Full functional dependency): All attributes must be fully dependent on the primary key c a b Then they can be placed in a table with ____ as pkey.

  25. FD WHEN DATA IS GIVEN When data is given, perform FD test: EMPLOYEES • ename --> phone#? • phone# --> ename? • ename à title? • employee à dependents?

  26. FD WHEN DATA IS GIVEN.. • a --> b? • a c? • b à c?

  27. FD WHEN DATA IS NOT GIVEN When data is not given, make case by case assumptions and perform FD test: • A flight (flt#) arrives or departs at one gate (gate#) • A flight (flt#) can have one captain (captain name) • A flight (flt#) can have one or more co-pilots (co-pilot name) • A flight (flt#) can go to multiple destinations (dest. name) • A flight (flt#) uses one or more altitudes (alt) • A flight (flt#) has one or more attendants (attdt_name) • A flight (flt#) has many crew members (cr_name) • A flight (flt#) lands at one or more airports (a_code) Note: FDs are split into pairs here for explanatory purposes

  28. FD WHEN DATA IS NOT GIVEN.. When data is not given, make case by case assumptions and perform FD test: • Cust places multiple orders • A flight has multiple pilots, but a single captain. • Each pet has a single owner • relationship between ss# and user name for an online stores? • User name, ss#, web site, company name, cust. credit card#

  29. DETERMINANTS & CANDIDATE KEYS Examples of FDs: SS# ---> Name, age, sex etc. Distance,Class --> Airfare. ISBN# --> Book title, price etc. The LHS of the FD is called a determinant, and is a candidate key. A candidate key is a key that can be used a pkey (usually LHS) Suppose a  b c -> b What are the determinants? candidate keys? design?

  30. THE FD APPROACH Suppose we have A --> B, C, D What will normalization yield? What is the foreign key here?

  31. FUNCTIONAL DEPENDENCY RULES 1. Reflexive rule x -- > y e.g. ISBN# --> ISBN# 2. Union rule If x -> y and x --> z then x--> yz e.g. ISBN# --> title and ISBN# --> price then ISBN# --> title, price 3. Transitivity rule If x--> y and y --> z then x--> z e.g. if VIN --> Model and Model --> Engine size then VIN --> Engine size;

  32. FUNCTIONAL DEPENDENCY RULES.. 4. Substitution rule If x -> y and yz --> w then xz --> w e.g. if model --> processor and processor, buswidth --> speed then model, buswidth --> speed equip#, p#  equip#, p# What rule is this? What rule is this? p#  descr P#  price => p#  descr, price

  33. FUNCTIONAL DEPENDENCY RULES.. If part#  descr serial#  part# Then serial#  descr What rule is this? a -> b and b  c then a  c What rule is this? a -> b and a  c then a  ??? What rule is this?

  34. FUNCTIONAL DEPENDENCY RULES.. Suppose the following FDs hold, a e à b, c, d f à a Then what is the primary key of the table b, c, d, e, f?

  35. FUNCTIONAL DEPENDENCY DIAGRAMS DRAWING FD DIAGRAMS The first step in design using FD approach is to draw an FD diagram: • List all attributes (horizontally) placing candidate key leftmost • Take each attribute (after candidate key) and find out its determinant • if data is given, do the FD test with the data. • if no data is given, make assumptions on a case by case basis. • For each value of an attr, how many of the other attr. are there? • If FD exists, draw single arrow • if no FD do not draw the relationship • if there are multiple values for each a, then draw a double arrow

  36. NORMALIZATION PROCESS THE FUNCTIONAL DEPENDENCY APPROACH • Identify and diagram the functional dependencies • Group functional dependencies according to their determinants • Place each set of FDs (along with determinants) in a separate table. • Use determinants as the pkeys. • Add appropriate foreign keys based on the application.

  37. AN EXAMPLE OF FD APPROACH Draw FD between a bank id (bid), location, manager (mgr_nm), # of employees and Manager join date (mgr_join_dt) F.D. diagram: bid  location, mgr_nm, # of emp., mgr_join_dt. Group FDs according to common determinants: bid  location, mgr_nm, # of emp. mgr_nm  mgr_join_dt Prepare design using notation below: Bank(bid, location, mgr_nm, #of emp) Manager(mgr_nm, mgr_join_dt) Check for cross reference keys

  38. FOR DISCUSSION Draw an FD diagram and develop a design for the following situation: Strauble-Dytisa 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

  39. Non key: A key that is not a pkey REVIEW OF CONCEPTS An attribute whose value is unique within an entity class (table) e.g. SS#, Part# etc. Primary key: Candidate key: A key that can serve as the primary key Composite key: A key that consists of more than one attribute e.g. E#, P# Foreign key/ A key that serves as reference between two tables Cross-reference key:

  40. REVIEW OF CONCEPTS.. Ill Structured: one that is poorly designed; or has redundancies or more than one value at row-column intersection Well Structured: one that is well designed; has no redundancies and atomic values at row-column intersection Anomaly: An inconsistency during a database activity insert, delete or update Determinant: LHS of a Functional Dependency (taken as candidate key) Functional Dependency: A relationship between two or more attributes such that if we know a, we can uniquely determine b FD Test: For each value of a there is one and only one b Full FD: All attributes are fully functionally dependent on pkey

More Related