1 / 20

Database Design

Database Design. Database Design. The process of developing database structures from user requirements for data a structured methodology Structured Methodology - a number of ordered formal processes with known inputs and expected outputs Objectives 1. derive relationships

gdonahue
Télécharger la présentation

Database Design

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. Database Design

  2. Database Design • The process of developing database structures from user requirements for data • a structured methodology • Structured Methodology - a number of ordered formal • processes with known inputs and expected outputs • Objectives • 1. derive relationships • 2. evolve to meet user requirements • 3. Do it right the first time!

  3. Database Design Goals • Reduce data redundancy. • Provide stable data structures that can be readily changed with changing user requirements. • Allow users to make ad hoc requests for data. • Maintain complex relationships between data elements. • Support a large variety of decision needs

  4. Database Design • data availability • data reliability • data currency • data consistency • data flexibility • data efficiency • Verify these criteria are satisfied via technical review

  5. Logical Data Modeling • 3 types of data objects: • Entities • Attributes • Relationships • ENTITIES: Are persons, places, or things about which data is to be, or is, gathered • ATTRIBUTES: Are the properties of entities.Examples are Names, Tax Numbers, Age, Status • RELATIONSHIPS : Describe how entities relate to each other eg Customers BUY Products • Persons WORK_ON Jobs

  6. Entities... • Are persons, objects or events about which information is, or will be, recorded in the Database • The designation of a ‘thing’ about which data is to be collected, stored or processed. • Many of these Entities can be identified with Business Activities (e.g. suppliers, purchase orders, customer)

  7. 3 Steps in Design Designing a database involves choosing: 1. The tables that belong in the database. What are the entities? 2. The columns that belong in each table. What are the properties? 3. How tables and columns interact with each other. What do they have in common?

  8. Database Design Functional Requirements Other matters Domains, Nulls, Derived Data, Encoded Data. Data Base Model Information Level Design Processes Final Information Level Design Physical Constraints Physical Level Design Final Database Structure

  9. Logical Data Modeling 2 entities can be related as : One to One : (1 : 1) The interpretation of this is that an occurrence of an entity ‘A’ can relate to ONE and ONLY ONE occurrence of entity ‘B’, and an occurrence of entity ‘B’ can relate to ONE and ONLY ONE occurrence of entity ‘A’ One to Many : (1: N) The interpretation is that ONE occurrence of entity ‘A’ can relate to ONE or MORE occurrences of entity ‘B’, but an occurrence of entity ‘B’ can relate to ONE ONLY occurrence of entity ‘A’

  10. Logical Data Modeling Many to Many (N : N)- a common business relationship The interpretation is that ONE or MORE occurrences of entity ‘A’ can relate to ONE or MORE occurrences of entity ‘B’ and ONE or MORE occurrences of entity ‘B’ can relate to ONE or MORE occurrences of entity ‘A’

  11. Entity Relationships Type Shown As Example One to One1:1 Book -----> Title One to Many1:N Publisher ---->Books Many to ManyN:NBooks <--->Authors

  12. Normalization Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating two factors: redundancy and inconsistent dependency.

  13. Unnormalized Table Student# Advisor Adv-Room Class1 Class2 Class3 ------------------------------------------------------- 1022 Jones 412 101-07 143-01 159-02 4123 Smith 216 201-01 211-02 214-01

  14. First Normal Form • Eliminate repeating groups in individual tables. • Create a separate table for each set of related data. • Identify each set of related data with a primary key.

  15. No Repeating Groups Student# Advisor Adv-Room Class# --------------------------------------- 1022 Jones 412 101-07 1022 Jones 412 143-01 1022 Jones 412 159-02 4123 Smith 216 201-01 4123 Smith 216 211-02 4123 Smith 216 214-01

  16. Second Normal Form • Create separate tables for sets of values that apply to multiple records. • Relate these tables with a foreign key.

  17. Eliminate Redundant Data The following two tables demonstrate second normal form: Students: Student# Advisor Adv-Room ------------------------------ 1022 Jones 412 4123 Smith 216 Registration: Student# Class# ------------------ 1022 101-07 1022 143-01 1022 159-02 4123 201-01 4123 211-02 4123 214-01

  18. Third Normal Form • Eliminate fields that do not depend on the key.

  19. Eliminate data not dependant on key Students: Student# Advisor ------------------- 1022 Jones 4123 Smith Faculty: Name Room Dept -------------------- Jones 412 42 Smith 216 42

  20. End Product of Data Base Design • A database which will: • Accurately reflect the ‘real world’ data in all • required aspects • Be responsive to Management information demands • Reflect Business Rules and Controls • Be capable of modification to meet changes • in Management needs • Be an asset to the Organization/Enterprise

More Related