Appendix c designing databases edited by y e jeff zhang csun
This presentation is the property of its rightful owner.
Sponsored Links
1 / 55

APPENDIX C DESIGNING DATABASES {Edited by Yüe “Jeff” Zhang, CSUN} PowerPoint PPT Presentation


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

APPENDIX C DESIGNING DATABASES {Edited by Yüe “Jeff” Zhang, CSUN}. LEARNING OUTCOMES. Describe the purpose of the relational database model in a database management system List the relational database model’s basic components

Download Presentation

APPENDIX C DESIGNING DATABASES {Edited by Yüe “Jeff” Zhang, CSUN}

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


Appendix c designing databases edited by y e jeff zhang csun

APPENDIX C

DESIGNING DATABASES

{Edited by Yüe “Jeff” Zhang, CSUN}


Learning outcomes

LEARNING OUTCOMES

  • Describe the purpose of the relational database model in a database management system

  • List the relational database model’s basic components

  • Describe the way entities and attributes are organized in a database

  • Familiarized with entities and attributes with real-world examples


Learning outcomes1

LEARNING OUTCOMES

  • Explain the need for an entity-relationship diagram in a database management system

  • Describe the Chen model symbols used in entity-relationship modeling

  • Understand types of relationships (1 to many etc); understand relationships among tables, primary key-foreign key

  • Three operations in relational database


Introduction

INTRODUCTION

  • The core chapters introduced:

    • Database-maintains information about various types of objects (products), people (employees), events (transactions), and places/organizations (warehouses/companies) 【see also C-7】

    • Database management system (DBMS) – creates, reads (“retrieves”), updates, and deletes data in a database while controlling access and security

    • Relational database model - a type of database that stores its information in the form of logically-relatedtwo-dimensional tables


Appendix c designing databases edited by y e jeff zhang csun

Data Hierarchy

(Figure from Laudon & Laudon)

High

Low

Bad exmpl - A principle of DB design: smlst elmt…

C-13


Entities and data relationships

ENTITIES AND DATA RELATIONSHIPS

  • Data model – The logical data structures that detail the relationships among data elements using graphics or pictures

  • The underlying relationships in a database environment:

    • Independent of the data model

    • Independent of the DBMS that is being used

  • Entity-relationship diagram (ERD) - A technique for documenting the relationships between entities in a database environment 【Beginning from C-19】

    • Entity:   

    • Attribute:   


Entities and their attributes

ENTITIES AND THEIR ATTRIBUTES

  • Entity - Also called a table, stores information about a type of things, persons, places/orgs, transactions, or events – Discussion: C8

    • Entity instance – a member in an entity: C9~C10

  • Attribute – Data elements associated with an entity; describe/define the entity from aspects related to business context: C8~C18

  • Think: an example for each type of entity above

    A CUSTOMER entity can be described by a Customer Number, First Name, Last Name, Street, City, State, Zip Code, Phone Number {Think - 1, “Address”? 2, Zip}


Discussion

DISCUSSION

  • Database-maintains information about various types of objects, places, people, and events

Think: City names in State table? Similarly: UNIV, COLLEGE, FACULTY, STUDENT


Entities and their attributes also entity instances

ENTITIES AND THEIR ATTRIBUTES;{also: entity instances}

{Edited & revised by Zhang}


Entities and entity instances

ENTITIES AND entity instances

{Edited & revised by Zhang}


Appendix c designing databases edited by y e jeff zhang csun

Entities, Attributes, and Records

Fields

Records; instances of the entity

4345

02/09/01

1765

8

13.75

Value of attributes / fields

{Edited by Zhang}


Type of attributes

Type of ATTRIBUTES

  • There are several types of attributes including:

    • Simple versus composite

    • Single-valued versus multi-valued

    • Stored versus derived

    • Null-valued

      • Some attributes canNOT be null

        • Such as?


1 simple versus composite

1. SIMPLE VERSUS COMPOSITE

  • Composite attributes can be divided into smaller subparts, which represent more basic attributes that have their own meanings

  • Example:Address

    • Address can be broken down into a number of subparts, such as Street, City, State, Zip Code

    • Street may be further broken down by Number, Street Name, and Apartment/Unit Number

  • Attributes that are not divisible into subparts are called simple attributes

  • Refer back to C-5


    Simple versus composite

    SIMPLE VERSUS COMPOSITE


    2 single valued versus multi valued

    2. SINGLE-VALUED VERSUS MULTI-VALUED

    • Single-valued attribute means having only a single value of each attribute of an entity at any given time

    • Example:

      • A CUSTOMER entity allows only one Telephone Number for each CUSTOMER

      • If a CUSTOMER has more than one Phone Number and wants them all included in the database the CUSTOMER entity cannot handle them


    Single valued versus multi valued

    SINGLE-VALUED VERSUS MULTI-VALUED

    • Multi-valued attribute means having the potential to contain more than one value for an attribute at any given time

    • Relational databases do not allow multi-valued attributes because they can cause problems:

      • Confuses the meaning of data in the database

      • Significantly slow down searching

      • Place unnecessary restrictions on the amount of data that can be stored

    How to handle N phone numbers?


    3 stored versus derived

    3. STORED VERSUS DERIVED

    • If an attribute can be calculated using the value of another attribute, it is called a derived attribute

    • The attribute that is used to derive the attribute is called a stored attribute

    • Derived attributes are not stored in the file, but can be derived when needed from the stored attributes

    • Example: A person’s age – 【think how】


    4 null valued

    4. NULL-VALUED

    • Null-valued attribute – Assigned to an attribute when no other value applies or when a value is unknown

    • Example: A person who does not have a cell phone - Cell Phone Number value?

    • More practical use of null value:

      • Sold date of properties – what if null?

      • Transaction price of items being auctioned

      • Returned date of library books – what if null?


    Documenting entity relationship diagrams

    DOCUMENTING ENTITY-RELATIONSHIP DIAGRAMS

    • Most commonly used styles of ERD notation: 1, Chen; 2, Information Engineering (Crow’s foot)

    • The Chen model uses rectangles to represent entities

      • Each entity's name appears in the rectangle and is expressed in the singular, as in CUSTOMER

    • Attributes are expressed in ovals

    • Relationships are expressed w diamonds


    Basic data relationships chen model

    BASIC DATA RELATIONSHIPS- Chen model


    Basic data relationships

    BASIC DATA RELATIONSHIPS

    • The relationships that are stored in a database are between instances of entities – “any ONE member of entity A…”   


    Basic data relationships1

    BASIC DATA RELATIONSHIPS

    • Once the basic entities and attributes have been defined, the next task is to identify the relationships among entities

    • There are three basic types of relationships:

      • One-to-one

      • One-to-many

      • Many-to-many

    Must be read from entity A to entity B, AND then from B to A, to decide.

    Only examining one direction cannot determine


    Documenting relationships the chen method

    DOCUMENTING RELATIONSHIPS – THE CHEN METHOD

    • The Chen method uses diamonds for relationships and lines with arrows to show the type of relationship between entities


    Documenting relationships the info engineering method

    DOCUMENTING RELATIONSHIPS – THE Info Engineering METHOD

    • The Info Engineeringmethod uses lines for relationships, and “crow’s foot” to show the type of cardinality (1 vs many) between entities

    Many


    One to one

    ONE-TO-ONE

    • One-to-one (1:1) – A relationship between two entities in which an instance of entity A can be related to only one instance of entity B; and entity B can be related to only one instance of entity A

    Chen

    Has (1)

    Located-in (1)

    Info

    Engi

    TOWN

    Has

    AIRPORT


    Data relationships zhang edited

    CAR

    OWNER

    Data Relationships (Zhang, edited)

    Entity-Relationship

    Diagram

    Relationships areassociationsbetweendatabase entities

    L-NAME

    ID

    F-NAME

    VIN

    MAKE

    ID

    ADDR

    owns

    M

    1

    MODEL

    (and more)

    YEAR

    (and more)

    This entity relationship represents the statement:

    "each owner may own many cars [left-to-right];

    each car is owned by one owner [right-to-left]"


    One to many

    ONE-TO-MANY

    • One-to-many (1:M) – A relationship between two entities, in which an instance of entity A (any instance of A), can be related to zero, one, or more instances of entity B; andan instance of entity B can be related to only one instance of entity A

    Has (many)

    Is-place-by (1)


    Appendix c designing databases edited by y e jeff zhang csun

    How many suppliers are there?

    Info Engring (Crow’s foot)

    Each supplier

    can supply

    many parts;

    Each part

    is supplied by

    Only one

    supplier

    SUPPLIER

    DEPT

    1

    1

    M

    M

    COURSE

    PART

    1

    1

    M

    M

    ORDER

    ENROLLMENT


    Many to many

    MANY-TO-MANY

    • Many-to-many (M:N)– A relationship between two entities in which an instance (any instance) of entity A can be related to zero, one, or more instances of entity B; ANDan instance of entity B can be related to zero, one, or more instances of entity A

    Orders (many)

    Chen

    Is-ordered-by (many)


    Many to many contents repeated show info engi method

    MANY-TO-MANY (contents repeated; show Info Engi method)

    • Many-to-many (M:N)– A relationship between two entities in which an instance (any instance) of entity A can be related to zero, one, or more instances of entity B; ANDan instance of entity B can be related to zero, one, or more instances of entity A

    TOWN

    AIRPORT

    Orders (many)

    Info

    Engi

    Is-ordered-by (many)


    Relational data model and the database

    RELATIONAL DATA MODEL AND THE DATABASE

    • Once the ERD is completed, it can be translated from a conceptual logical schema into the formal data model required by the DBMS

    • Every “box” (entity) in ERD corresponds to one “relation” (table) in the relational data model


    From entities to tables

    FROM ENTITIES TO TABLES

    • The word “table” is used synonymously with “entity”: an entity is implemented as …

      • Attributes are implemented as …

      • Instances are implemented/shown as …

    • The definition (of table) specifies what will be contained in each column of the table, but does not include data

      • What other term do we use to refer to a column in a table?

      • {what are there to describe an entity?}

    C-11; also C36


    From entities to tables1

    FROM ENTITIES TO TABLES

    • A row in a relation has the following properties:

      • Only one value at the intersection of a column and row - a relation does not allow multi-valued attributes

      • Uniqueness - there are no duplicate rows in a relation

      • Primary key - A field (or group of fields) that uniquely identifies a given entity in a table

    When would we see this?


    From entities to tables2

    FROM ENTITIES TO TABLES

    • A unique primary key makes it possible to uniquely identify every row in a table

    • The primary key is important to define and to retrieve every single piece of data in a database

    • There are only three pieces of information to retrieve for any specific bit of data:

      • The name of the table

      • The name of the column

      • The primary key of the row


    From entities to tables3

    FROM ENTITIES TO TABLES

    • The proper notation to use when documenting the name of the table, the column name, and primary key:

      • CUSTOMER(Customer Number, First Name, Last Name, Phone Number)

      • Will follow this notation for DB project

    • Three qualities of all primary keys:

      • A primary key should contain some value that is highly unlikely ever to be null

      • A primary key should never change

      • Primary key for all rows have distinct values


    Logically relating tables

    LOGICALLY RELATING TABLES

    • The use of iden-tifiers repres-ent relation-ships between entities

    Primary key;

    Foreign key


    Logically relating tables1

    LOGICALLY RELATING TABLES

    • When a table contains a column that is the same as the primary key of another table, the column is called a foreign key

    • Foreign key - A primary key of one table that appears as an attribute in another file, and acts to provide a logical relationship between the two files {common column to join two tables}

    • Example: Prim. Key CUSTOMER(Customer Number, First Name, Last Name, Phone Number)

      • ORDER(Order Number, Customer Number, Order Date)

    Refers to

    For. key


    Appendix c designing databases edited by y e jeff zhang csun

    ThreeBasic Operations in a Relational Database

    • Project:Extracts subset of columnsto create new tables (“views”)

      • EX: display only last name and GPA

    • Select:Extracts subset of rows that meet specific criteria

      • Numeric: salary<40000;

      • text: city=‘LA’;

      • date: DOB=#12/12/1972#

    • Criteria can be combined using AND, OR, etc

  • Join:Combines relational tablesusing foreign keywhen the data needed is not in one table

  • Next slide shows examples of the three operations  

  • {Edited & revised by Zhang}


    Appendix c designing databases edited by y e jeff zhang csun

    Three Basic Operations in a Relational Database (Laudon)

    Project

    Select

    Join


    Creating database objects

    Creating Database Objects

    • Four objects: table, query, report, form

    • Report – a compilation of data from the database that is organized and produced in printed format

      • Present data in a prescribed format

      • So data must be obtained from ___?

      • Changing the report format … changing data?

        Relationships between the four objects:

    Forms

    (Entry, update)

    Reports

    (Presentation)

    Queries (Data Extraction)

    Tables (Data storage)

    {Edited & revised by Zhang}


    Dealing with many to many relationships

    DEALING WITH MANY-TO-MANY RELATIONSHIPS

    • There are problems with many-to-many relationships

      • The relational data model cannot handle many-to-many relationships directly

        • It is limited to one-to-one and one-to-many relationships

        • Many-to-many relationships need to be replaced with a collection of one-to-many relationships

      • Relationships cannot have attributes

        • An entity must represent the relationship

        •    composite entities


    Composite entities optional

    COMPOSITE ENTITIES (optional)

    • Composite entities - Entities that exist to represent the relationship between two other entities, AKA Intersection entities

      • Intersection entities are used in the resolution of a many to many relationship

    • Example: between an ITEM and an ORDER

      • An ORDER can contain many ITEM(s) and over time, the same ITEM can appear on many ORDER(s)


    Composite entities intersection entities optional

    COMPOSITE ENTITIES / Intersection Entities (optional)


    Composite key example zhang

    Composite Key – Example (Zhang)

    Situation: a customer buys certain products on a certain date

    • Can customer-ID uniquely identify an order line?

    • Can product-ID uniquely identify an order line?

    • Can date uniquely identify an order line?

    • (Answers: …)

    • Composite key: C-ID, P-ID, Date

    {Edited & revised by Zhang}


    Normalization

    Edited & revised by Zhang

    Normalization

    Normalization is a method for analyzing and reducing a relational database to its most streamlined form for:

    Minimum redundancy

    Maximum data integrity

    Best processing performance

    Normalized data is when attributes in the table depend only on the primary key.


    Non normalized relation

    Non-Normalized Relation


    Normalizing the database

    Normalizing the Database


    Normalizing the database1

    Normalizing the Database


    Normalization produces order

    Normalization Produces Order


    Appendix c designing databases edited by y e jeff zhang csun

    {Edited & revised by Zhang}

    Example 2: AnUNnormalized Relation of ORDER

    • If a part appears in many orders, Part_Num, Part_Desc, and Unit_Price will appear in everyone of these orders

    • If a part appears in many orders, its supplier info – Supp_Num, Supp_Name, Supp_Address - will also appear in every one of these orders

    • Lots of data redundancy


    Appendix c designing databases edited by y e jeff zhang csun

    {Edited & revised by Zhang}

    Example 2: An Normalized Relation of ORDER

    Data redundancy eliminated – the data elements mentioned in previous slide only need to be stored in their own tables, and can be retrieved through “join” operation


    New slides 2014 1 entity etc

    New slides 2014 (1)– Entity etc

    • The correspondence of business and DB


    New slides 2014 2 correspondence

    New slides 2014(2)– Correspondence

    • The correspondence of terms in DB


    New slides 2014 3 connection

    New slides 2014(3)– Connection

    Primary key and foreign key connect tables

    • Relationship exists in 1-M

    • Primary key of 1-side must be in M-side to be foreign key

    • FACULTY : STUDENT = 1 : M

    • Primary key of FACULTY is FID

    • FID must be in STUDENT table to be foreign key


    New slides 2014 3 connection1

    New slides 2014(3)– Connection

    Primary key and foreign key (cont)


  • Login