1 / 37

Chapter 5: Relational Database

Chapter 5: Relational Database. Logical Design. Learning Objectives. You should be able to … Transform any ERD into a series of relations complete with Primary keys Foreign keys Explain each of these concepts: Domain integrity Entity integrity Referential integrity

channary
Download Presentation

Chapter 5: Relational Database

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. Chapter 5:Relational Database Logical Design

  2. Learning Objectives You should be able to … • Transform any ERD into a series of relations complete with • Primary keys • Foreign keys • Explain each of these concepts: • Domain integrity • Entity integrity • Referential integrity • Describe 1NF, 2NF, 3NF • Redesign relations so that they are all 3NF

  3. Relational Databases (Codd, 70) • Structural Aspect: viewed as Relations (Tables) (Physical structure hidden from user). • Integrity Constraints: Relations satisfy certain integrity constraints. • Manipulative Aspect: Set of Operators that derive a relation from relations.

  4. Terminology (definitions) • Relation = Table • Tuples = Rows • Cardinality = Number of Rows of a Relation • Degree = Number of Columns of a Relation

  5. More terminology • Primary Key: an attribute (or combination of attributes) that uniquely identifies each row in a relation • Composite Key: a primary key consisting of more than one attribute • Foreign Key: an attribute in a relation of a database that serves as the primary key of another relation in the same database • Referential integrity constraint: A rule that states that either each foreign key value must match a primary key value in the other relation or else the foreign key value must be null.

  6. Relational Definitions Relation • Every relation has a unique name. • Every attribute value is atomic. • Every row is unique. • Attributes in tables have unique names. • The order of the columns is irrelevant. • The order of the rows is irrelevant. EMPLOYEE(EmployeeID, EmployeeName)

  7. From ERD to Relation (Table) 1st step. EMPLOYEE (EmployeeID, EmployeeName) CERTIFICATE (Certificate_Number, Date_Completed, EmployeeID, Course_ID) COURSE (Course_ID, CourseTitle) What next ???

  8. Pine ValleyM:N unresolved ORDER CUSTOMER PRODUCT

  9. ORDER ORDER-LINE CUSTOMER PRODUCT Pine Valley ERDM:N resolved

  10. Relational Schema for Pine Valley Furniture: One way of illustrating the Database Design

  11. Another way to illustrate the Database Design CUSTOMER (CustomerID, CustomerName, CustomerAddress) ORDER (OrderID, OrderDate, CustomerID) ORDERLINE (OrderID, ProductID, Quantity) PRODUCT (ProductID, Description, Finish, Unit_Price, On_Hand)

  12. Integrity Constraints • Domain Constraints • Allowable values for an attribute. • Entity Integrity • No primary key attribute may be null. • Operational Constraints • Business rules. • Referential Integrity • For example: Delete Rules • Restrict (default) • Cascade • Set-to-Null

  13. Mapping a composite attribute (a) CUSTOMER entity type with composite attribute

  14. Multivalued attribute

  15. Example of mapping a 1:M relationship Relationship between customers and orders

  16. Example of mapping an M:N relationship Requests relationship (M:N)

  17. Mapping a binary 1:1 relationship Binary 1:1 relationship

  18. Mapping an associative entity with an identifier Associative entity (SHIPMENT)

  19. Mapping a ternary relationship (a) Ternary relationship with associative entity

  20. Supertype/subtype relationships

  21. Well-Structured Relations AVOID/REDUCE • Insertion Anomaly • Deletion Anomaly • Modification Anomaly

  22. E-R Diagrams -> Relations (Tables) • Binary 1:N Relationships • Two possible options a.Add the primary key attribute (or attributes) of the entity on the one side of the relationship as a foreign key in the relation on the right side The one side migrates to the many side. (default) b..Create another relation (3rd table) and include primary keys of all (both) relations as primary key of new relation • Binary or Unary 1:1 • Three possible options • Add the primary key of A as a foreign key of B • Add the primary key of B as a foreign key of A c.Create another relation (3rd table) and include primary keys of all (both) relations as primary key of new relation

  23. E-R Diagrams to Relations • Represent Relationships (continued) • Binary M:N relationships • Create another table and include primary keys of all tables as primary key of new relation • Unary 1:N Relationships 2 options • Relationship between instances of a single entity type. Utilize a recursive foreign key • A foreign key in a table (relation) that references the primary key values of that same table (relation) • Create another table and include primary keys of both tables as primary key of new table (relation) • Unary M:N Relationships • Create a separate relation • Primary key of new relation is a composite of two attributes that both take their values from the same primary key

  24. Transforming ER to Relations (Tables) • 1 : 1 (3 options) (FK either way, new table) • 1 : N (2 options) (FK on child, new table) • N : M (1 option) (new table) • Generalization 1 table for each sub-type + 1 for super-type 1 table for each sub-type 1 big table

  25. Data Normalization • Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data. • The process of decomposing relations with anomalies to produce smaller, well-structured relations.

  26. Functional Dependencies and Keys • Functional Dependency: The value of one attribute (the determinant) determines the value of another attribute. • Candidate Key: Each non-key field is functionally dependent on every candidate key.

  27. Steps in normalization

  28. First Normal Form • No multi-valued attributes. • Every attribute value is atomic. • Figure 5-2a (p.168). Is it in 1NF ?

  29. Second Normal Form • 1NF and every non-key attribute is fully functionally dependent on the primary key. • Every non-key attribute must be defined by the entire key, not by only part of the key. • No partial functional dependencies. • Fig. 5-2b.(p.168) Is it in 2NF ?

  30. Third Normal Form • 2NF and no transitive dependencies (functional dependency between non-key attributes.) • Fig. 5-24 (p.194)

  31. Relation with transitive dependency (a) SALES relation with simple data

  32. (b) Transitive dependency in SALES relation

  33. Removing a transitive dependency (a) Decomposing the SALES relation

  34. (b) Relations in 3NF

  35. Goals of Normalization • Reduce data redundancy • Improve “modify” activities: • insert, • update, • delete, • but not read • Price: degraded query, display, reporting

  36. Steps in Normalization • 1NF: a table, without multivalued attributes • if not, then decompose • 2NF: 1NF and every non-key attribute is fully functionally dependent on the primary key • if not, then decompose • 3NF: 2NF and no transitive dependencies • if not, then decompose • GENERAL: • Each table should describe a single theme • Modification anomalies are minimized

  37. Exercises • Go to http://coffee.kennesaw.edu and execute all the Animations under Database Design Hw # 3 (turn in your homework typed!)

More Related