database requirements and design l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Database Requirements and Design PowerPoint Presentation
Download Presentation
Database Requirements and Design

Loading in 2 Seconds...

play fullscreen
1 / 39

Database Requirements and Design - PowerPoint PPT Presentation


  • 405 Views
  • Uploaded on

Database Requirements and Design. DATA. PEOPLE. HARDWARE. SOFTWARE. PROCEDURES. The Product: a working system. Database development activities during the systems development life cycle (SDLC). Project Identification and Selection. Project Initiation and Planning. Analysis.

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 'Database Requirements and Design' - emily


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
slide2

DATA

PEOPLE

HARDWARE

SOFTWARE

PROCEDURES

The Product: a working system

slide3

Database development activities during the systems development life cycle (SDLC)

Project Identification

and Selection

Project Initiation

and Planning

Analysis

Logical Design

Physical Design

Implementation

Maintenance

slide4

Project Identification & Selection

  • Purpose: to develop a preliminary understanding of the business situation that has caused the request for a new or enhanced information system.
  • Deliverable: a formal request for services
project identification and selection
Project Identification and Selection
  • Printing Company: use internet to request quotes, place orders and check order status
  • Video Game Store: track customer information, track customer purchases, maintain inventory
  • Elementary school After School program: student records, important information, account balances
  • Interpreter’s Organizer: assignment status, income, costs, taxes
project identification and selection6
Project Identification and Selection
  • Groups of 3-5, talk to me if you are thinking about an individual assignment
  • I would advise you use either a devise a case study with your group where you implement a database, or use a company/scenario that someone in your group is familiar with. Make sure everyone in the group understands the scenario
  • The next step in your group process should be to sit down and talk about the System Requirement. Make sure everyone in your group understands what data you want to get from the database.
project initiation and planning
Project Initiation and Planning

System Requirements:

  • Information requirements of the system
  • Stakeholders
  • Constraints
  • Application environment
slide8

Case Study

  • The Clearwater Traders Sales Order Database
slide9

Overview

  • Markets a line of clothing and sporting goods via mail-order catalogs
  • To remain competitive, company managers want to create a database to allow Internet customers to:
    • Browse the catalog on-line
    • Place orders
    • Check order status status
slide10

Information Requirements

  • System must do all of the following:
    • Track information about customers and customer orders
    • Track customer orders
    • Track inventory information so customers can find out if an item is available
    • Have intranet applications, so Clearwater Traders personnel can:
      • Add, update, and delete inventory items
      • Update inventory amounts when new shipments are received
      • Modify item prices
slide11

Stakeholders

  • Company management
  • Clearwater Traders personnel
  • Customers
slide12

Constraints

  • System must be operational by April 2001
  • System must not require additional purchase of software/hardware

Other possible constraints? Training employees, …

slide13

Application Environment

  • Access 2000 on networked PCs
  • Expansion to Web sites using Visual Basic and Personal Web Server
slide14

Logical Design

  • Purpose: to elicit and structure all information requirements
  • Deliverables: detailed functional specs for data, forms, reports, displays, and rules (logic, algorithms)
principles of conceptual database design
Principles of Conceptual Database Design
  • Identify basic data needs and create conceptual data models that record needed data elements and their relationships
  • Your local Bank:
    • How many checking accounts do we have?
    • How many savings accounts?
    • How many customers?
    • How do we get each customer the right monthly statement?
    • How do I get a weekly report of overdue loan payments?
reality and requirements definition
Reality and Requirements Definition
  • Requires identifying user information requirements and representing them in a well-defined model
    • Model – a representation of reality that retains only selected details
  • Database Management System (DBMS) manages the database so that each user can record, access, and manipulate the data that is the “model of reality” of interest to the user
    • Requires mapping – associating elements in one sphere with elements in another sphere
conceptual data models
Conceptual Data Models
  • Object-oriented model
    • Represents real-world entities as objects rather than as records
      • Have their own object identities and attributes
      • Participate in relationships
  • Describes ways of manipulating data that focuses on the data and the manipulation (query, computation, update, etc.)
fundamentals
Fundamentals
  • Objects
    • Important to users in the portion of reality modeled
      • Concrete objects
        • People, automobiles, trees, dishwashers, houses, hammers, books, etc.
      • Conceptual objects
        • Companies, skills, organizations, product designs, business transactions, job classifications, etc.
entity relationship diagram
Entity Relationship Diagram

An entity-relationship diagram is a data modeling technique that creates a graphical representation of the entities, and the relationships between entities, within an information system.

the three main components of an erd are
The three main components of an ERD are:
  • The entity is a person, object, place or event for which data is collected
  • The relationship is the interaction between the entities
  • The cardinality defines the relationship between the entities in terms of numbers.
the steps involved in creating an erd are
The steps involved in creating an ERD are:
  • Identify the entities;
  • Determine all significant interactions;
  • Analyze the nature of the interactions;
  • Draw the ERD
slide22

Synonyms you should know…

Entity = class = relation = table

Attribute = column

Instance = row

columns

table

rows

relationships
Relationships
  • MARRIED-MAN IS-MARRIED-TO MARRIED-WOMAN
    • MARRIED-MAN = {Adam, David, John} and
    • MARRIED-WOMAN = {Joan, Linda, Michelle} and
    • Adam is-married-to Joan
    • David is-married-to Linda
    • John is-married-to Michelle
  • IS-MARRIED-TO =
    • { (Adam, Joan), (David, Linda), (John, Michelle) }
representations of a relationship
Representations of a Relationship

IS

MARRIEDTO

Married

Man

Married

Woman

(a) The IS MARRIED TO relationship

Adam ----------------------------------- Joan

David ----------------------------------- Linda

John ----------------------------------- Michelle

(b) Some instances of IS MARRIED TO

cardinality
Cardinality
  • Refers to the maximum number of instances of 1 object set related to a single instance of the other object set

IS

MARRIEDTO

Married

Man

Married

Woman

1

1

1:1 relationship

Supervises

Supervisor

Worker

1

*

1:M or 1:* relationship

slide31

Relationship types and instances

(a) Relationship type “Completes”

slide32

Cardinality constraints

(a) Relationship showing maximum cardinality only.

slide33

Cardinality constraints

(b) Relationship showing max & min cardinality

building a conceptual data model from a purchase order
Building a Conceptual Data Model from a Purchase Order

Check #

Number

Address

VENDOR

PAYMENT

Name

Stock #

Date

PRODUCT

ORDER

Number

Tax

Description

Price

Date

Total

1

PAID WITH

1

1

MADE FROM

*

* INCLUDES *

representing tables entities
Representing Tables (entities)
  • Vendor (Number, Name, Address)
  • Product (Stock #, Description, Price)
  • Order (Number, Date, Tax, Total)
  • Payment (Check #, Date)

Attributes are in parentheses

Primary key is underlined.

slide36

Physical Design

  • Purpose: to develop all technology & organizational specs
  • Deliverables: program & database structures, technology purchases, physical site plans and organizational redesigns
slide37

Implementation

  • Purpose: to write programs, build data files, test and install the new system, train users, and finalize documentation
  • Deliverables: programs that work accurately and to specs, documentation (user and system), and training materials.
slide38

For Practice...

  • Identify a potential key value for each of the following entities:
    • Customer (Cust-Name, Cust-ID, Address, Country, Balance, Month-to-date-payments)
    • Sales Rep (Comm%, Office, Manager-ID, Sales Rep Name, Sales Rep ID)
    • Product (Prod-Desc, Mfg-ID, Cost, Price, Prod-ID)
slide39

For Practice...

  • For each of the following, draw a data model showing a relationship between objects and attributes of the objects. Indicate cardinality.
    • Students take classes and get grades in the classes.
    • Sections of courses are offered at specified times and in specified rooms of buildings.
    • Employees work a certain number of hours and have a certain supervisor.
    • People subscribe to magazines, and their subscriptions have beginning and ending dates.