Week 8 october 19
Download
1 / 42

Week 8 October 19 - PowerPoint PPT Presentation


  • 97 Views
  • Uploaded on

Week 8 October 19. Database Design Modeling with ERD. Administration. Data Administrator (DA) – management of the data resources, including the database planning, development, and maintenance of standards, policies and procedures, and conceptual and logical database design

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Week 8 October 19' - Pat_Xavi


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
Week 8 october 19

Week 8October 19

Database Design

Modeling with ERD


Administration
Administration

  • Data Administrator (DA) – management of the data resources, including the database planning, development, and maintenance of standards, policies and procedures, and conceptual and logical database design

  • Database Administrator (DBA) – management of the physical realization of a database system, including physical database design and implementation, setting security and integrity controls, monitoring system performance, and reorganizing the database (when necessary)


Database design
Database Design

  • Data modeling

    • Understanding the meaning of data

      • Identify the user’s perspective of data

      • Identify the data themselves

      • Identify the applications supported by the data

    • Communication information requirements

      • Diagram with ERD (entity-relationship diagram)

Satisfying the information needs of the organization


Optimal logical design criteria
Optimal Logical Design Criteria

  • Structural validity - reflects the enterprise

  • Simplicity - ease of understanding

  • Expressability - distinguishability of data

  • Nonredundancy - exclusion of extraneous information

  • Shareability - nonexclusive data

  • Extensibility - support future information requirements

  • Integrity - consistency with organization’s information use and management

  • Diagrammatic representation - ability to graphically model data


Logical vs physical design
Logical vs. Physical Design

  • Logical

    • Defines the whats (e.g., what information needs to be present)

  • Physical

    • Defines the hows (e.g., how data will be stored)

What

How

Sequence


Fact finding techniques
Fact-Finding Techniques

  • Examining documents

  • Interviewing

  • Observing the enterprise in operation

  • Research

  • Questionnaires


Design tools
Design Tools

Relational database design

  • Entity relationship diagram (ERD)

    • Relations, relationships, constraints

  • Data normalization

    • Method for establishing relations

For relational model only

For relational database only



Entity relationship er model applies to relational data model
Entity Relationship (ER) Model(applies to relational data model)

  • High-level conceptual model

    • Describes the structure of the database, and the associated retrieval and update transactions on the database

    • Composed of

      • Entity types

      • Relationship types

      • Attributes


Er modeling
ER Modeling

Relationship type

Products

stock number

product description

retail price

stock on hand

stock on order

Manufacturers

manufacturer code

manufacturer name

Have

0..*

1..1

Attributes

Entity type


Er modeling alternatively
ER ModelingAlternatively

Relationship type

Products

Stock number

Product description

Retail price

Stock on hand

Stock on order

Manufacturers

Manufacturer code

Manufacturer name

Attributes

Entity type


Erd notation
ERD Notation

Primary key

Entity type

Relationship type

Relationship name

Music_categories

CDs

Classify

music_category_code {PK}

music_category_title

Attributes

stock_number {PK}

CD_title

artist

music_category_code

record_label_code

1..1

0..*

Multiplicity (constraint)

Degree of the Relationship: Binary


Erd notation alternatively
ERD NotationAlternatively

Primary key (underscored)

Entity type

Relationship type

Relationship name

Music_categories

CDs

Music_category_code

Music_category_title

Attributes

Classify

Stock_number

CD_title

Artist

Music_category_code

Record_label_code

Zero

(circle)

Minimum

(inside)

Cardinality

Maximum

(outside)

Many

(crows feet)


1 entity types
1. Entity Types

  • Strong Entity Type

    • Not existence-dependent on another entity type

  • Weak Entity Type

    • Existence-dependent on another entity type (i.e., child, dependent, subordinate)

Entity type

Entity

Entity

Uniquely identifiable

Entity


Entity types
Entity Types

Strong entity?

Music_categories

CDs

Music_category_code

Music_category_title

Classify

Stock_number

CD_title

Artist

Music_category_code

Record_label_code


Definition of a weak entity type
Definition of a Weak Entity Type

“An entity type that borrows all or part of its primary key. Identifying relationships indicate the entity types that supply components of the borrowed primary key.”

Mannino, 1999

Entity type 1

Key

attributes...

Method to Follow

Have

Weak entity type

Entity type 2

Key

Key

attributes...

Composite key


Diagramming weak entity types
Diagramming Weak Entity Types

An account cannot exist without an customer.

Customers

attributes...

Strong entity type (parent, owner, dominant)

Minimum must be one

Customer_Accounts

attributes...

Weak entity entity (child, dependent, subordinate)

*A customer can have more than one account

Designates a weak entity type


2 attributes
2. Attributes

Property of an entity or relationship type

  • Attribute domain

    • Set of values that may be assigned to a single-valued attribute

Customers

Cust_account Cust_name Cust_address Cust_phone Soc_Sec_Num

Customer_Accounts

Cust_account Current_balance Credit_limit Active_date Expire_date


Attributes of attributes
Attributes of Attributes

  • Simple (atomic attributes) - composed of a single component

  • Composite - composed of multiple components

  • Single valued - one value for an entity

  • Multi-valued - one or more values for an entity

  • Derived - value derived from a related attribute or set of attributes

Student_ID FName MName LName

Single-valued

Multi-valued

Student_ID Semester Course_ID

More than one semester, more than one course_id


Attribute domain
Attribute Domain

Customers

Composite

Cust_account Cust_name Cust_address Cust_phone Soc_Sec_Num

  • On an ER model, should customer name be shown as a composite or simple attribute?

  • What is the attribute domain of Cus_name?

Cust_first_name

Cust_last_name

John

William

Anita

Homer

Brown

Tell

Breake

Simpson


Derived attributes
Derived Attributes

  • Derived - value derived from a related attribute or set of attributes

Student_ID Semester Course_ID Units Grade Grade_point

Student_ID Semester Course_ID Units Grade Grade_point

Student_ID Semester Course_ID Units Grade Grade_point

Units x Grade = Grade point


Attributes as keys
Attributes as Keys

Uniquely identifies an entity

Candidate key

  • Keys cannot change their values (good for the life of the entity)

  • An efficient means for identifying an entity

Primary key

  • Alternate key - candidate that can also be used to access an entity

  • Composite key - composed of multiple attributes (components)


Diagrammatic representation
Diagrammatic Representation

Customers

Cust_account {PK}

Cust_name

First_name

Middle_name

Last_name

Cust_address

Street_number

Zip_code (fk)

Cust_phone

Soc_sec_num

Key

Composite attribute

Composite attribute

Foreign key


3 relationship types
3. Relationship Types

  • A set of associations between two (or more) participating entity types

  • Each is given a name that describes the function

Customers

Customer_account

Own

Customers_accounts

Customer_account


Entity relationship diagram
Entity Relationship Diagram

  • Degree of a relationship - number of entities participating in a relationship (binary, ternary, quaternary, etc.)

Customers

Customer_account

Strong

Relationship

Own

  • “Dog-ear” lines indicate a relationship between a weak and strong entity

Customers_accounts

Customer_account

Weak


Data modeling
Data Modeling

Music_categories

Music_category_code

Music_category_title

Strong Entity (parent)

All children (CDs) must have a parent (music categories or record labels)

Relationship

Classify

Strong Entity (parent)

CDs

Stock_number

CD_title

Artist

Music_category_code (fk)

Record_label_code (fk)

Record_labels

Record_label_code

Record_label

Produce

Weak Entity

(child)

Method to Follow


Degree of a relationship
Degree of a Relationship

Customers

A customer purchases products and places them on his/her account

Products

Buy

Relationship of degree three or ternary

Cust_Accounts


Degree of a relationship1
Degree of a Relationship

An employee is managed by only one manager (an employee is related to a maximum and minimum of one manager)

Manages

Employees

Employee_number

Employee_name

Classification

Project_ID

Self-referencing relationship

A manager manages one to many employees (a manager is related to a minimum of one and a maximum of many employees)


Structural constraints
Structural Constraints

  • Cardinality

    • Determines the number of possible relationships for each participating entity

      • 1:1 - one to one

      • 1:M - one to many

      • M:N - many to many

  • Participation

    • Determines whether the existence of an entity depends upon its being related to another entity through the relationship

Defined by business rules


Cardinality
Cardinality

  • 1:1 (one to one)

    • Each entity in X is associated with at most one entity in Y and conversely each entity in Y is associated with at most one entity in X

  • 1:M (one to many)

    • Each entity in X can be associated with many entities in Y but each entity in Y is associated with at most one entity in X.

  • M:N (many to many)

    • Each entity in X can be associated with many entities in Y and each entity in Y can be associated with many entities in X.


Cardinality1
Cardinality

1:1 Relationships

Strong entity type

Weak entity type

Customers

Accounts

Customer_ID

Customer_name

Customer_address

Zip_code

Account_number

Customer_ID

Account_type

Current_balance

Own

Mandatory participation

A customer owns a minimum and maximum of one account

An account is owned by a minimum and maximum of one customer

Note. This would be avoided in the logical design, but could be implemented in the physical.


Cardinality2
Cardinality

1:M Relationships

Strong entity type

Weak entity type

Customers

Accounts

Customer_ID

Customer_name

Customer_address

Zip_code

Account_number

Customer_ID

Account_type

Current_balance

Own

Mandatory participation

A customer owns a minimum one and maximum of many accounts

An account is own by a minimum and maximum of one customer

Note. This would be avoided in the logical design, but could be implemented in the physical.


Cardinality3
Cardinality

  • M:N relationship if a customer can own more than one account (e.g., revolving, long-term), and one account can have more than one owner (e.g., joint account).


Cardinality4
Cardinality

M:N Relationships

Strong entity type

Weak entity type

Customers

Accounts

Customer_ID

Customer_name

Customer_address

Zip_code

Account_number

Customer_ID

Account_type

Current_balance

Own

Mandatory participation

A customer owns a minimum of one and a maximum of many accounts

An account is owned by a minimum of one and a maximum of many customers

Note. This would be avoided in the logical design, but could be implemented in the physical.


Participation constraints
Participation Constraints

  • Determines whether the existence of an entity depends on it being related to another entity through the relationship

    • Total (mandatory) - If the existence of one requires another

    • Partial (optional) - If the existence of one does not require the other

Existence Dependency: An entity that cannot exist unless another related entity exists. A mandatory relationship produces an existence dependency.

Mannino, 1999


Erd notation1
ERD Notation

Primary key (underscored)

Entity type

Relationship type

Relationship name

Music_categories

CDs

Music_category_code

Music_category_title

Attributes

Classify

Stock_number

CD_title

Artist

Music_category_code

Record_label_code

Zero

(circle)

A CD is related to a minimum and maximum of one music category

Minimum

(inside)

Cardinality

Maximum

(outside)

Many

(crows feet)


Erd notation2
ERD Notation

Primary key (underscored)

Entity type

Relationship type

Relationship name

Music_categories

CDs

Music_category_code

Music_category_title

Attributes

Classify

Stock_number

CD_title

Artist

Music_category_code

Record_label_code

Zero

(circle)

A music category is related to a minimum of zero and maximum of many CDs

Minimum

(inside)

Cardinality

Maximum

(outside)

Many

(crows feet)


Erd notation3
ERD Notation

Minimum cardinality of one

(a music category has to have at least one CD)

Entity type

Music_categories

CDs

Music_category_code

Music_category_title

Classify

Stock_number

CD_title

Artist

Music_category_code

Record_label_code

Weak entity type

(all four corners)


Erd notation4
ERD Notation

Music_categories

CDs

Music_category_code

Music_category_title

Classify

Stock_number

CD_title

Artist

Music_category_code

Record_label_code

A record label is related to a minimum of zero and maximum of many CDs

Produce

Record_labels

Record_label_code

Record_label


Erd notation5
ERD Notation

Music_categories

CDs

Music_category_code

Music_category_title

Classify

Stock_number

CD_title

Artist

Music_category_code

Record_label_code

Produce

A CD is related to a minimum and maximum of one record label

Record_labels

Record_label_code

Record_label


Erd notation6
ERD Notation

Music_categories

CDs

Music_category_code

Music_category_title

Classify

Stock_number

CD_title

Artist

Music_category_code

Record_label_code

Quantity_produced

Produce

Attribute of a relationship

Record_labels

Record_label_code

Record_label