1 Database Design ASPPDATABASE DESIGN 2 Objectives Define

1 Database Design ASPP(DATABASE DESIGN)

2 Objectives à Define the terms entity, record, and attribute and discuss the various types of keys à Draw an entity-relationship diagram, and explain the types of entity relationships à Define cardinality, cardinality notation, and crow’s foot notation à Explain normalization, including examples of first, second, and third normal form ASPP(DATABASE DESIGN)

3 Objectives à Compare and contrast database management and file processing environments à Explain various types of database organization, including hierarchical, network, and relational design à Describe various types of files, including master, transaction, table, work, history, and security ASPP(DATABASE DESIGN)

4 Introduction à Data terminology and concepts à Relationships among data objects à Entity-relationship diagrams (ERDs) ASPP(DATABASE DESIGN)

5 Data Terminology and Concepts à Data design includes à Entities à Fields à Records à Keys ASPP(DATABASE DESIGN)

6 Data Terminology and Concepts à Definitions à Entity: a person, place, thing, or event for which data is collected and maintained à Field (attribute): a single characteristic or fact about an entity à Record: a collection of fields that describes one instance of an entity ASPP(DATABASE DESIGN)

7 ASPP(DATABASE DESIGN)

8 Data Terminology and Concepts à Definitions à Key field: a field used to locate, retrieve, or identify a specific record à Primary key: a key that uniquely identifies each record ASPP(DATABASE DESIGN)

9 Data Terminology and Concepts à Key fields à Primary keys à A field or combination of fields that uniquely and minimally identifies each member of an entity à A primary key composed of more than one field is called a multivalued key ASPP(DATABASE DESIGN)

10 ASPP(DATABASE DESIGN)

11 Data Terminology and Concepts à Key fields à Candidate keys à Any field that could serve as primary key à Any field that is not a primary key or candidate key is called a nonkey field ASPP(DATABASE DESIGN)

12 Data Terminology and Concepts à Key fields à Foreign keys à A field in one file that matches a primary key value in another file à Example: the advisor number is a foreign key in the STUDENT file that matches a primary key value in the ADVISOR file à A foreign key need not be unique à A combination of two or more foreign keys can form a unique primary key value à Referential integrity ensures that a foreign key value cannot be entered unless it matches a primary key value in another file ASPP(DATABASE DESIGN)

13 Data Terminology and Concepts à Key fields à Secondary keys à A field or combination of fields that can be used to access or retrieve records à Secondary keys do not need to be unique ASPP(DATABASE DESIGN)

Data Relationships and Entity. Relationship Diagrams à Entity-relationship diagrams (ERDs) à An ERD is a graphical model that shows relationships among system entities ASPP(DATABASE DESIGN) 14

15 ASPP(DATABASE DESIGN)

Data Relationships and Entity. Relationship Diagrams 16 à Entity-relationship diagrams (ERDs) à An ERD is a graphical model that shows relationships among system entities à Each entity is a rectangle, labeled with a noun à Each relationship is a diamond, labeled with a verb à Types of relationships à One-to-one (1: 1) à One-to-many (1: M) à Many-to-many (M: N) à A full ERD shows all system relationships ASPP(DATABASE DESIGN)

17 ASPP(DATABASE DESIGN)

Data Relationships and Entity. Relationship Diagrams 18 à One-to-one (1: 1) relationship à Exists when exactly one of the second entity occurs for each instance of the first entity à Examples à One office manager heads one office à One vehicle ID number is assigned to one vehicle à One driver drives one delivery truck à One faculty member is chairperson of one department ASPP(DATABASE DESIGN)

19 ASPP(DATABASE DESIGN)

Data Relationships and Entity. Relationship Diagrams 20 à One-to-many (1: M) relationship à Exists when one occurrence of the first entity can be related to many occurrences of the second entity, but each occurrence of the second entity can be associated with only one occurrence of the first entity à Examples à One individual owns many automobiles à One customer places many orders à One department employs many employees à One faculty advisor advises many students ASPP(DATABASE DESIGN)

21 ASPP(DATABASE DESIGN)

Data Relationships and Entity. Relationship Diagrams à Many-to-many (M: N) relationship 22 à Exists when one instance of the first entity can be related to many instances of the second entity, and one instance of the second entity can be related to many instances of the first à Examples à A student enrolls in one or more classes, and each class has one or more students registered à A passenger buys tickets for one or more flights, and each flight has one or more passengers à An order lists one or more products, and each product is listed on one or more orders ASPP(DATABASE DESIGN)

23 ASPP(DATABASE DESIGN)

Data Relationships and Entity. Relationship Diagrams 24 à A full ERD shows all system relationships à Examples à A sales rep serves one or more customers, but each customer has only one sales rep à A customer places one or more orders, but each order has only one customer à An order lists one or more products, and each product can be listed in one or more orders à A warehouse stores one or more products, and each product can be stored in one or more warehouses ASPP(DATABASE DESIGN)

25 ASPP(DATABASE DESIGN)

Data Relationships and Entity. Relationship Diagrams 26 à Cardinality à Describes how instances of one entity relate to another à Mandatory vs. optional relationships à Crow’s foot notation is one method of showing cardinality ASPP(DATABASE DESIGN)

27 ASPP(DATABASE DESIGN)

Data Relationships and Entity. Relationship Diagrams 28 à Cardinality à Describes how instances of one entity relate to another à Mandatory vs. optional relationships à Crow’s foot notation is one method of showing cardinality à Most CASE products support the drawing of ERDs ASPP(DATABASE DESIGN)

29 ASPP(DATABASE DESIGN)

Data Relationships and Entity. Relationship Diagrams 30 à Creating an ERD 1. Identify the entities 2. Determine all significant events or activities for two or more entities 3. Analyze the nature of the interaction 4. Draw the ERD ASPP(DATABASE DESIGN)