Agenda for week 1 31 2 2
This presentation is the property of its rightful owner.
Sponsored Links
1 / 29

Agenda for Week 1/31 & 2/2 PowerPoint PPT Presentation


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

Agenda for Week 1/31 & 2/2. Learn about database design Vocabulary Modeling tool: Entity relationship diagram (ERD) Practical modeling concepts Do database design Practice creating ERD’s Primarily from “draftsman” perspective. Database Design.

Download Presentation

Agenda for Week 1/31 & 2/2

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


Agenda for week 1 31 2 2

Agenda for Week 1/31 & 2/2

  • Learn about database design

    • Vocabulary

    • Modeling tool: Entity relationship diagram (ERD)

    • Practical modeling concepts

  • Do database design

    • Practice creating ERD’s

    • Primarily from “draftsman” perspective


Database design

Database Design

  • Goal for the database design section of the class: Be able to design a “good” database for a business application.

  • Objectives:

    • Know how to read, understand, and create a database model using a modeling tool - ERD’s.

    • Understand the three reasons we store data in a database.

    • Know the process of completing a database design.

    • Understand the structure and limitations of the relational model.

    • Know how to identify and differentiate the required components of a database design.


What is database design

What is database design?

  • Database design is the process of planning the structure or blueprint of stored data for an organization.


Process of database design

Process of database design

  • Can be understood from three different perspectives:

    • Architect: Planning types of data necessary to achieve the goals of the system.

    • Engineer: Identifying detailed data requirements and designing the structure of the data.

    • Draftsman: Creating a model to depict the data that will be stored in a database.


Data design is documented with a data model

Data design is documented with a data model

  • Most commonly used data modeling tool is an entity-relationship diagram (ERD).

  • An ERD contains:

    • Entities.

    • Attributes.

    • Identifiers (primary key for each entity).

    • Relationships.

    • Cardinalities of the relationships.

    • Foreign keys to support the relationships.


All about entities

All About Entities

  • Entities are nouns that describe the person, event, place, or thing about which we want to store data.

  • The name of an entity is singular, not plural. Examples: customer, book, order, invoice, employee, supplier.

  • An entity usually becomes a table in a database.

  • An entity instance is a single occurrence of an entity (a row in a table, or a record in a file).

  • Entities almost always have more than one entity instance. (Tables have more than one row – it is not common to create a table that has only one row.)

    • For example, you might be interested in storing information about the “chief financial officer” of an organization. If there is just one CFO, then you wouldn’t create an entity for CFO, you would create an entity for “employee” and figure out another way to store the fact that a specific employee is the CFO.


Attributes

Attributes

  • An attribute is a characteristic or property of an entity.

  • Synonyms include:

    • Element, property or field.

  • Examples of attributes for an “employee” entity:

    • Last name, First Name, Employee ID, Address, City, State, Zip, Phone, Title, Starting employment date.

  • Attributes can be:

    • Stored vs. derived.

    • Simple or complex.


Identifying attributes identifiers

Identifying Attributes (Identifiers)

  • An attribute that could uniquely identify an instance of an entity is called a “candidate key”.

    • A candidate key that the analyst chooses to uniquely identify an instance of an entity is called a “primary key”.

    • Primary keys are also referred to as identifiers.

  • Sometimes more than one attribute is needed to uniquely identify an instance of an entity.

    • A group of attributes identifying an entity instance is called a “concatenated key.”

    • Synonyms for concatenated key are “composite key” and “compound key”.


Primary keys

Primary keys

  • Each entity must have a primary key.

  • Two basic types of primary key:

    • “Natural” primary key: primary key is created from existing attribute or attributes.

    • “Surrogate” primary key: primary key is a “made up” attribute that has no function other than serving as a primary key.

  • For right now, we are going to try and use natural primary keys as much as possible.


A bit about relationships

A bit about relationships

  • Entities do not usually exist in isolation.

  • A connecting line between two entities on an ERD represents a relationship.

  • A relationship can be depicted as a diamond or as a simple line.

  • A relationship is a natural business association existing between two or more entities.

  • A relationship creates a business rule.

  • A verb phrase describes the relationship.


Cardinality of a relationship

Cardinality of a relationship

  • Cardinality is a constraint on the number of entity instances that participate in a relationship.

  • Cardinality describes the minimum and maximum number of instances that one entity has with another entity in a relationship.

  • Cardinality also describes whether the relationship is mandatory or optional.

  • We use the crow’s foot notation to depict cardinality in our ERD’s in this class.


Agenda for week 1 31 2 2

Examples: Binary relationship with explicit relationship symbol


Agenda for week 1 31 2 2

Examples: Binary relationship without explicit relationship symbol


Agenda for week 1 31 2 2

Examples of Unary Relationships


Agenda for week 1 31 2 2

Example of a ternary relationship


Agenda for week 1 31 2 2

Example of an n-ary relationship


Example of a context erd

Example of a context ERD


Agenda for week 1 31 2 2

Example of a logical ERD


Agenda for week 1 31 2 2

Example of a Physical ERD


Strong entity

Strong entity

  • A strong entity is an entity that exists whether or not there is a relationship between it and another entity.

  • For example, you probably want to store data about employees, regardless of the fact that an employee provides services to a patient at a clinic.


Weak or associative entities

“Weak” or Associative Entities

  • An entity that does not exist unless it is linked to a strong entity.

  • For example, the patient treatment entity doesn’t exist unless a patient has a treatment.

  • On a logical ERD, a weak entity frequently borrows all or part of its primary key from another entity.


Agenda for week 1 31 2 2

Return to First Week Exercise Data (condensed)


Need for associative entity

Need for Associative Entity

  • M:n relationship cannot be stored without data redundancy.

  • A m:n relationship usually has attributes that are part of the relationship. In the example below, you must decide where to store the quantity ordered for a given item on a given order.

  • Intersection entity is used to divide a m:n relationship.


Agenda for week 1 31 2 2

Possible Solution #1: Put the quantity ordered (qty_ordered) in the item entity, and create a concatenated primary key


Agenda for week 1 31 2 2

Possible solution#1: Results in redundant data for item name, cost and sell price


Agenda for week 1 31 2 2

Possible solution #2: Uses an intersection entity (Orderline) to avoid redundant data.


Agenda for week 1 31 2 2

Possible Solution #2: Another table, but less data redundancy


Foreign key

Foreign Key

  • A way of supporting the relationship between two tables.

  • The primary key of one table is added to another table to link the two tables together.

  • In a 1:M relationship, the primary key of the entity on the “1” side of the relationship is added to the entity on the “M” side of the relationship.


  • Login