Module C
This presentation is the property of its rightful owner.
Sponsored Links
1 / 27

Module C Designing Databases and Entity-Relationship Diagramming PowerPoint PPT Presentation


  • 79 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Module c designing databases and entity relationship diagramming

Module C

Designing Databases and Entity-Relationship Diagramming


Overview

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.


Introduction

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.


Designing and building a relational database

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.


Step 1 defining entity classes and primary keys

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.


Step 1 defining entity classes and primary keys1

Step 1: Defining Entity Classes and Primary Keys

  • What are the entity classes and primary keys for the report below?


Step 1 defining entity classes and primary keys2

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)


Step 2 defining relationships among entity classes

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


Step 2 defining relationships among entity classes1

Step 2: Defining Relationships Among Entity Classes

  • An E-R diagram for our Employee database.


Step 2 defining relationships among entity classes2

Team Work

Defining Relationships Among

Entity Classes

(p. 169)

Step 2: Defining Relationships Among Entity Classes


Step 2 defining relationships among entity classes3

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.


Step 2 defining relationships among entity classes4

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.


Step 2 defining relationships among entity classes5

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.


Step 2 defining relationships among entity classes6

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.


Step 2 defining relationships among entity classes7

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.


Step 2 defining relationships among entity classes8

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.


Step 2 defining relationships among entity classes9

Step 2: Defining Relationships Among Entity Classes

  • The intersection relation (Job Assignment) uses a composite primary key.


Step 2 defining relationships among entity classes10

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).


Step 2 defining relationships among entity classes11

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.


Step 2 defining relationships among entity classes12

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.


Step 2 defining relationships among entity classes13

Team Work

Creating an

Intersection Relation

(p. 174)

Step 2: Defining Relationships Among Entity Classes


Step 3 defining information fields for each relation

Step 3: Defining Information (Fields) for Each Relation


Step 3 defining information fields for each relation1

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.


Step 3 defining information fields for each relation2

Step 3: Defining Information (Fields) for Each Relation


Step 3 defining information fields for each relation3

On Your Own

Creating the Final Structure for Your School

(p. 177)

Step 3: Defining Information (Fields) for Each Relation


Step 4 using a data definition language to create your database

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.


Step 4 using a data definition language to create your database1

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.


  • Login