UPLOAD

    Muhammad Javed

    Lahore, Pakistan

    5.3K

    Database Design

    Published: September 21, 2018

    Database Design

    Comments

    Database Design

    • 1. Slide63 DESIGNING DATABASES AND ENTITY- RELATIONSHIP DIAGRAMMING
    • 2. STUDENT LEARNING OUTCOMES STUDENT LEARNING OUTCOMES 1.Identify how databases and spreadsheets are both similar and different 2.List and describe the four steps in designing and building a relational database 3.Define the concepts of entity class, instance, primary key, and foreign key
    • 3. STUDENT LEARNING OUTCOMES STUDENT LEARNING OUTCOMES 4.Given a small operating environment, build an entity-relationship (E-R) diagram 5.List and describe the steps in normalization 6.Describe the process of creating an intersection relation to remove a many-to- many relationship
    • 4. INTRODUCTION INTRODUCTION •Chapter 3 discussed why databases are important •This module teaches you how to design a relational database •The relational database is the most popular model
    • 5. INTRODUCTION INTRODUCTION •Databases and spreadsheets are similar and different •Both have rows and columns of information in tables or worksheets –Spreadsheet – you must know the physical row and column –Databases – you work with information logically
    • 6. INTRODUCTION INTRODUCTION •Database – 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
    • 7. DESIGNING & BUILDING A RELATIONAL DATABASE DESIGNING & BUILDING A RELATIONAL DATABASE 1.Define entity classes & primary keys 2.Define relationships among the classes 3.Define information for each relation (relation = table = file) 4.Use a data definition language to create the database
    • 8. Remember Solomon Enterprises? Remember Solomon Enterprises? •From Chapter 3 •It provides concrete to commercial builders and individual home owners •In Chapter 3, we looked at the customer relationship management side of Solomon’s database
    • 9. Remember Solomon Enterprises? Remember Solomon Enterprises?
    • 10. Remember Solomon Enterprises? Remember Solomon Enterprises? •Tables in Solomon’s database –Customer –Concrete Type –Order –Truck –Employee •Let’s continue with that example and expand to supply chain management (SCM)
    • 11. Observations for Solomon Observations for Solomon •5 concrete types 1.Home foundation and walkways 2.Commercial foundation and infrastructure 3.Premier speckled (with gravel) 4.Premier marble 5.Premier shell
    • 12. Observations for Solomon Observations for Solomon
    • 13. Observations for Solomon Observations for Solomon •6 raw materials A.Water B.Cement paste C.Sand D.Gravel E.Marble F.Shell •Mixing instructions are for a cubic yard
    • 14. Observations for Solomon Observations for Solomon •Some raw materials are in several concrete types •A concrete type requires several raw materials •Inventory (QOH) is tracked for all raw materials
    • 15. Observations for Solomon Observations for Solomon •Suppliers provide raw materials •Solomon uses only 1 supplier for a given raw material •A supplier can provide several raw materials
    • 16. Business Rules for Solomon Business Rules for Solomon 1.A given concrete type will have many raw materials in it 2.A given raw material may appear in many types of concrete 3.Each raw material has one and only one supplier
    • 17. Business Rules of Solomon Business Rules of Solomon 4.A supplier may provide many raw materials. There may be suppliers present not currently providing any raw materials •These business rules are very important to remember
    • 18. STEP 1: DEFINE ENTITY CLASSES AND PRIMARY KEYS STEP 1: DEFINE ENTITY CLASSES AND PRIMARY KEYS •Entity class – concept – typically people, places, or things – about which you wish to store information and that you can identify with a unique key (called a primary key) –Concrete Type –Raw Material –Supplier
    • 19. STEP 1: DEFINE ENTITY CLASSES AND PRIMARY KEYS STEP 1: DEFINE ENTITY CLASSES AND PRIMARY KEYS •Primary key – a field (or group of fields) that uniquely describes each record •A record in a database is sometimes called an instance (of an entity class)
    • 20. STEP 1: DEFINE ENTITY CLASSES AND PRIMARY KEYS STEP 1: DEFINE ENTITY CLASSES AND PRIMARY KEYS •In general, stay away from names for primary keys (duplicates often exist) •Concrete Type – Concrete Type •Raw Material – Raw Material ID •Supplier – Supplier ID •Review Figure C.1 on page 162 to find examples of these
    • 21. STEP 2: DEFINING RELATIONSHIPS AMONG ENTITY CLASSES STEP 2: DEFINING RELATIONSHIPS AMONG ENTITY CLASSES •To define relationships, you create and use an E-R diagram •Entity-relationship (E-R) diagram – a graphic method of representing entity classes and their relationships
    • 22. E-R Diagrams E-R Diagrams •Use 5 symbols 1.Rectangle – entity class 2.Dotted line – relationship 3.| - single relationship 4.0 – zero/optional relationship 5.Crow’s foot ( ) – multiple relationship
    • 23. E-R Diagrams E-R Diagrams
    • 24. E-R Diagrams E-R Diagrams •Figure C.2 on page 165 reads as… –A Concrete Type is composed of Raw Material –A Raw Material is used to create a Concrete Type –A Supplier provides a Raw Material –A Raw Material is provided by a Supplier •These statements match the business rules
    • 25. E-R Diagrams - Cardinality E-R Diagrams - Cardinality •E-R diagrams show relationships •They also denote the numerical nature of relationships •This is called cardinality •| - single relationship •0 – zero/optional relationship •Crow’s foot ( ) – multiple relationship
    • 26. E-R Diagrams - Cardinality E-R Diagrams - Cardinality Following line marked A: A Supplier may not provide any Raw Material (0) but may provide more than one Raw Material ( )
    • 27. E-R Diagrams - Cardinality E-R Diagrams - Cardinality Following line marked B: A Raw Material must be provided by a Supplier (|) and can only be provided by one Supplier (|)
    • 28. Normalization Normalization •Normalization – process of assuring that a relational database structure can be implemented as a series of two-dimensional tables •We will follow three rules of normalization
    • 29. Normalization Rules Normalization Rules 1.Eliminate repeating groups or many-to- many relationships 2.Assure that each field in a relation depends only on the primary key for that relation 3.Remove all derived fields from the relations
    • 30. Eliminating Many-To-Many Relationships Eliminating Many-To-Many Relationships •A many-to-many relationship exists in a relationship if there is a crow’s foot on each end •You must eliminate these by creating an intersection or composite relation
    • 31. Eliminating Many-To-Many Relationships C-31 Eliminating Many-To-Many Relationships
    • 32. Eliminating Many-To-Many Relationships Eliminating Many-To-Many Relationships •Intersection (composite) relation – a relation you create to eliminate a many-to- many relationship –Intersection relation will have a composite primary key •Composite primary key – consists of the primary key fields from the two intersecting relations
    • 33. Eliminating Many-To-Many Relationships Eliminating Many-To-Many Relationships
    • 34. Solomon’s New Database Structure Solomon’s New Database Structure
    • 35. Steps to Eliminate a Many-To-Many Relationship Steps to Eliminate a Many-To-Many Relationship 1.Draw the part of the E-R diagram with the many-to-many relationship 2.Write some primary keys for each relation 3.Create a new E-R diagram with the intersection relation in the middle 4.Write some composite primary keys for the intersection relation
    • 36. Steps to Eliminate a Many-To-Many Relationship Steps to Eliminate a Many-To-Many Relationship 5.Create a meaningful name for the intersection relation 6.Move minimum cardinality appearing next to left relation to the right of the intersection relation 7.Move minimum cardinality appearing next to right relation to the left of the intersection relation
    • 37. Steps to Eliminate a Many-To-Many Relationship Steps to Eliminate a Many-To-Many Relationship 8.Maximum cardinality on both sides of the intersection relation will always be many () 9.General rule – the new minimum and maximum cardinalities for the 2 original relations will be one (|) and one (|)
    • 38. STEP 3: DEFINING INFORMATION FOR EACH RELATION STEP 3: DEFINING INFORMATION FOR EACH RELATION •View Figure C.7 on page 174 •It is the correct and final structure •There are no many-to-many relationships •Each field depends only on the primary key for that relation •There are no derived fields •You have created a good database design
    • 39. STEP 4: USE A DATA DEFINITION LANGUAGE TO CREATE YOUR DATABASE STEP 4: USE A DATA DEFINITION LANGUAGE TO CREATE YOUR DATABASE •You’re finally ready to implement your database with a DBMS •Database management system (DBMS) – helps you specify the logical organization for a database and access and use the information within the database
    • 40. STEP 4: USE A DATA DEFINITION LANGUAGE TO CREATE YOUR DATABASE STEP 4: USE A DATA DEFINITION LANGUAGE TO CREATE YOUR DATABASE •Extended Learning Module J is devoted to using Microsoft Access to –Create the data dictionary for Solomon’s database –Enter information into the database –Create queries of information in the database –Create reports of information in the database
    • 41. CAN YOU… CAN YOU… 1.Identify how databases and spreadsheets are both similar and different 2.List and describe the four steps in designing and building a relational database 3.Define the concepts of entity class, instance, primary key, and foreign key
    • 42. CAN YOU… CAN YOU… 4.Given a small operating environment, build an entity-relationship (E-R) diagram 5.List and describe the steps in normalization 6.Describe the process of creating an intersection relation to remove a many-to- many relationship