Complex entities and relationships

1. Complex entities and relationships Case examples are from the book: Lars Fank Databaser Teori og Praksis ISBN 87-571-2397-7

2. The 7 most important slides in this course!

3. Repetition: One-to-many relationships

4. Repetition: Many-to-many relations.

5. In a tree structure relation, each node has at most one parent node.

6. The organization structure of companies is normally a tree structure.

7. In a production structure the relationship has data associated.

8. Different illustrations of tree structure relationships:

9. How would you implement the tree production structure with relatioship data in the relational model? How is it possible to find all the raw materials of a given product?

10. In a network relationship, a node may have any number of parents.

11. Different illustrations of network structure relationships:

12. Illustrate the contents of the Node and Branche tables for the first two levels of the production structure below.

13. The left diagram illustrates how to produce both logical and physical products. For example the “Input” relationship to the “Logical processes” should have an attribute that describes how much of the raw materials or semi-products that are normally used in the different processes of a production.The right diagram uses a network structure to illustrate the production process.Which implementation model of the production database do you prefer?

14. How would you implement a family tree that may be used to prevent inbreeding of animals. Exercise

15. Database of the ERP production module

16. Is the formal leadership of an organization a network or a tree structure? Is the informal leadership of an organization a network or a tree structure? Describe other examples of network or tree structures?

17. Phases in relationaldatabase design:

18. Primary key = Any unique identification attribute may be selected as the primary key. Forign key = an attribute that is a primary key in some other table. Entities are normally stored in tables together with their attributes. A relationship from one entity to another may be implemented by storing the primary key of the related entity as a forign key.

19. Candidate key =any minimal set of attributes that may be used as a primary key. Nonprime attribute = Any attribute that is not part of a candidate key. Key definitions: Determinant key = Candidate key for some of the nonprime attributes.

20. BCNF = If a table has more determinant keys they may all be used as the primary key. The problem with the third normal form (3NF) is that it does not allow more than one candidate key in a table, and that is not acceptable. Boyce-Codd Normal Form (BCNF): Determinant key = Candidate key for some of the nonprime attributes.

21. Candidate key =any minimal set of attributes that may be used as a primary key. Nonprime attribute = Any attribute that is not part of a candidate key. Is the employee table on Boyce-Codd Normal Form (BCNF)? Determinant key = Candidate key for some of the nonprime attributes.

22. End of session Thank you !!!