1 / 27

Module C Designing Databases and Entity-Relationship Diagramming

Module C Designing Databases and Entity-Relationship Diagramming. Overview. Designing and Building A Relational Database The four primary steps for designing a database include: Defining entity classes and primary keys. Defining relationships among entity classes.

lareina-garrett
Télécharger la présentation

Module C Designing Databases and Entity-Relationship Diagramming

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. Module C Designing Databases and Entity-Relationship Diagramming

  2. Overview • Designing and Building A Relational Database • The four primary steps for designing a database include: • Defining entity classes and primary keys. • Defining relationships among entity classes. • Defining information (fields) for each relation. • Using a data definition language to create your database.

  3. Introduction • Database - a collection of information that you organize and access according to the logical structure of that information. • Relational database - uses a series of logically related two-dimensional tables or files to store information in the form of a database.

  4. Designing And Building A Relational Database • The four primary steps include: • Defining entity classes and primary keys. • Defining relationships among entity classes. • Defining information (fields) for each relation. • Using a data definition language to create your database.

  5. Step 1: Defining Entity Classes and Primary Keys • Entity class - a concept - typically people, places, or things - about which you wish to store information and that you can identify with a unique key (called the primary key). • Primary key- a field (or group of fields in some cases) that uniquely describes each record. • Instance - an occurrence of an entity class that can be uniquely described.

  6. Step 1: Defining Entity Classes and Primary Keys • What are the entity classes and primary keys for the report below?

  7. Team Work Defining Entity Classes and Primary Keys (p. 167) Step 1: Defining Entity Classes and Primary Keys • Employee (primary key – Employee ID) • Job (primary key – Job Number) • Department (primary key Department Num)

  8. Step 2: Defining Relationships Among Entity Classes • Entity-relationship (E-R) diagram - a graphic method of representing entity classes and their relationships. • Rectangle – entity class • Dotted line – relationship • | - single relationship • O – zero or optional relationship • Crow’s foot () – multiple relationship

  9. Step 2: Defining Relationships Among Entity Classes • An E-R diagram for our Employee database.

  10. Team Work Defining Relationships Among Entity Classes (p. 169) Step 2: Defining Relationships Among Entity Classes

  11. Step 2: Defining Relationships Among Entity Classes • Employee-Department: An Employee is assigned to one Department at a minimum and one Department at a maximum.

  12. Step 2: Defining Relationships Among Entity Classes • Department-Employee: A Department is not required to have any Employees assigned to it but may have many Employees assigned to it.

  13. Team Work Defining the Cardinality Among Entity Classes (p. 169) Step 2: Defining Relationships Among Entity Classes • All statements you derive from an E-R diagram should mirror the business rules at hand.

  14. Step 2: Defining Relationships Among Entity Classes • Now it’s time to employ normalization. • Normalization - a process of assuring that a relational database structure can be implemented as a series of two-dimensional relations.

  15. Step 2: Defining Relationships Among Entity Classes • Three rules of normalization: • Eliminate repeating groups or many-to-many relationships. • Assure that each field in a relation depends only on the primary key for that relation. • Remove all derived fields from the relations.

  16. Step 2: Defining Relationships Among Entity Classes • To eliminate repeating groups (many-to-many relationships) you must, create an intersection relation. • The figure on the next slide shows a many-to-many relationship and the intersection relation that eliminates it.

  17. Step 2: Defining Relationships Among Entity Classes • The intersection relation (Job Assignment) uses a composite primary key.

  18. Step 2: Defining Relationships Among Entity Classes • Intersection relation - a relation you create to eliminate a many-to-many relationship. • Composite primary key - consists of the primary key fields from the two intersecting relations. • Foreign key - a primary key of one file (relation) that appears in another file (relation).

  19. Step 2: Defining Relationships Among Entity Classes • Guidelines for creating an intersection relation: • Draw the part of the E-R diagram that contains a many-to-many relationship. • Underneath each relation for which the many-to-many relationship exists, write down some of the primary keys. • Create a new E-R diagram (showing no cardinality) with the original two relations on each end and a new one in the middle. • Underneath the intersection relation, write down some composite primary keys.

  20. Step 2: Defining Relationships Among Entity Classes • Create a meaningful name for the intersection relation. • Move the minimum cardinality appearing next to the left relation just to the right of the intersection relation. • Move the minimum cardinality appearing next to the right relation just to the left of the intersection relation. • The maximum cardinality on both sides of the intersection relation will always be “many”. • The new minimum and maximum cardinalities for the two original relations will be one and one.

  21. Team Work Creating an Intersection Relation (p. 174) Step 2: Defining Relationships Among Entity Classes

  22. Step 3: Defining Information (Fields) for Each Relation

  23. Step 3: Defining Information (Fields) for Each Relation • Department Name does not belong in the Employee relation. • It depends on Department Num. • It does not depend on Employee ID. • Num of Employees does not belong in the Department relation. • It can be derived by counting employees in the Employee relation.

  24. Step 3: Defining Information (Fields) for Each Relation

  25. On Your Own Creating the Final Structure for Your School (p. 177) Step 3: Defining Information (Fields) for Each Relation

  26. Step 4: Using a Data Definition Language to Create Your Database. • The final step in developing a relational database is to take the structure you created in steps 1 to 3 and use a data definition language to actually create the relation. • Read Module J on the CD to learn how to do this with Microsoft Access.

  27. Step 4: Using a Data Definition Language to Create Your Database • The data definition language is part of the DBMS. • Database management system (DBMS) - helps you specify the logical organization for a database and access and use the information within the database.

More Related