DATABASE DESIGN

1
DATABASE DESIGN

• Database
• Carefully designed and constructed repository of
  facts
• Part of an information system

• Information System
• Provides data collection, storage, and
  retrieval

• Information systems components
• hardware
• software
• data
• processes
• people

2
Changing Data into Information

• Data
• Raw facts stored in databases
• Need additional processing to become useful
• Information
• Required by decision maker
• Data processed and presented in a meaningful form
• Transformation (any process that changes data
  into information).

Program instructions
3
The Information System

• Information System
• Provides data collection, storage, and retrieval
• Facilitates the transformation of data into
  information
• Facilitates the management of both data and
  information

4
The Information System (Cont.)

• Database development
• Process of database design and implementation
• Database Design Creation of database models
  (complete, normalized, nonredundant)
• Implementation
• Creating storage structure
• Loading data into database
• Providing for data management

5
The Database Life Cycle (DBLC)
6
The Database Life Cycle (DBLC)

• Phase 1 The Database Initial Study
• The overall purpose is to
• Analyze the company situation,
• Operating environment and its mission
• Organizational structure
• Define problems and constraints,
• Define objectives,
• Define scope and boundaries.
• The database initial study phase leads to the
  development of the database system objectives.

7
A Summary of Activities in the Database Initial
Study
8
The Database Life Cycle (DBLC)

• Phase 1 The Database Initial Study
• Analyze the company situation
• Analysis to break up any whole into its parts
  so as to find out their nature, function, and so
  on.
• The issues to be resolved
• What is the organizationss general operating
  environment, and what is its mission within that
  environment?
• (operational demands created by the
  organizations mission. i.e. mail- order
  business, manufacturing, etc.)
• What is the organizations structure?
• (Knowing who controls what and who reports to
  whom is quite useful when you are trying to
  define required information flows, specific
  report and query formats)

9
The Database Life Cycle (DBLC)

• Phase 1 The Database Initial Study
• Define Problems and Constraints
• Formal sources of information...
• If there is an existing system How does the
  existing system function? What input does the
  system require? What documents does the system
  generate? How is the system output used? By whom?
  (Studying the paper trail)
• Informal sources of information...
• Company end users are often unable to describe
  the larger scope of company operations or
  identify the real problems encountered during
  company operations.
• Managerial view of a company operation is
  different from that of the end users who perform
  the actual routine work.

10
The Database Life Cycle (DBLC)

• Phase 1 The Database Initial Study
• Define Problems and Constraints
• President of manufacturing company... has
  problems to maintain high customer-services
  standards. Decreasing manufacturing standards
  control.
• Marketing manager... can not give quick responses
  to customer calls, can not do part search easily.
• Production Manager... it takes hours to generate
  reports for production schedules.
• It is not possible to match inventory and
  production schedules, so they sometimes produce
  parts that are already in inventory.
• It takes days even weeks to get reports to
  schedule personnel, training, etc.
• Quick updates on percent defectives, percent
  rework, the effectiveness of training, etc.
• The designer is likely to collect broad problem
  definitions.
• Finding appropriate answers is important,
  especially about the (operational) relationships
  among business units.

Example
11
The Database Life Cycle (DBLC)

• Phase 1 The Database Initial Study
• Define Objectives
• To solve at least the major problems identified
  during the problem discovery process.
• Several common sources can be discovered...
• i.e. If both the production manager and
  marketing manager have problems with inventory
  inefficiencies, the initial objective might be to
  create an efficient inventory query and
  management system.
• The initial study phase yields proposed problem
  solutions.
• Address the following questions
• What is the proposed systems initial objective?
• Will the system interface with other existing or
  future systems in the company?
• Will the system share the data with other
  systems or users?

12
The Database Life Cycle (DBLC)

• Phase 1 The Database Initial Study
• Define Scope and Boundaries
• The systems scope defines the extent of the
  design, according to operational requirements.
  Will the database system covers the entire
  organization, one or more departments, or one or
  more functions of a single department?
• The scope helps define the required data
  structures, the type and number of entities, etc.
• Boundaries are external to the system.
• No designer has unlimited time, budget and
  resources!
• Boundaries are also imposed by existing hardware
  and software.

13
The Database Life Cycle (DBLC)
Phase 2 The Database Design

• Most Critical DBLC phase
• Makes sure that the final product meets user and
  system requirements
• Focus on data requirements
• Two views of data within the system
• The business view of data as a source of
  information
• The designers view of the data structure, its
  access and the activities required to transform
  the data into information.

14
Two Views of Data Business Manager and Designer
15
The Database Life Cycle (DBLC)
Phase 2 The Database Design

• Subphases
• Create conceptual design
• DBMS software selection
• Create logical design
• Create physical design

16
Subphases of Database Design
17
The Database Life Cycle (DBLC)
Phase 2 The Database Design

• I. Conceptual Design
• Data modeling is used to create abstract data
  structure to represent real-world items
• Make sure that
• All that is needed is there, and all that is
  there is needed!
• Software-independent
• Steps...
• Data analysis and requirements
• Entity relationship modeling and normalization

18
The Database Life Cycle (DBLC)

• Data analysis and requirements
• Focus on
• Information needs (What kind of information is
  needed that is, what output (reports and
  queries) must be generated by the system?)
• Information users (Who will use the information?
  How is the information to be used? What are the
  different end-user data views?)
• Information sources (Where is the information to
  be found? How is the information to be extracted
  once it is found?)
• Information constitution (What data elements are
  needed to produce the information? What are the
  data attributes? What relationships exist among
  the data? What data transformations are to be
  used to generate the required information?)

19
The Database Life Cycle (DBLC)

• Data analysis and requirements
• Find answers to the questions from different data
  sources

• Developing and gathering end-user data views
• Direct observation of current system / Review the
  existing documents (forms, reports, etc.)
• Business rules (a description of a policy,
  procedure, or principle within an organizations
  environment)

20
The Database Life Cycle (DBLC)
Entity Relationship Modeling and Normalization
21
The Database Life Cycle (DBLC)
Phase 2 The Database Design

• II. Logical Design
• Used to translate the conceptual design into a
  selected DBMS, such as DB2, SQL Server, Oracle,
  Access.
• Software-dependent
• Design components
• Tables, attribute domains, etc.

22
The Database Life Cycle (DBLC)
Database Design Strategies
23
THE UNIVERSITY LAB CONCEPTUAL DESIGN

• How to develop a database initial study
• How to write a description of operations
• How to write business rules on which the
  database design is based
• How to translate the business rules into ERD
  segments
• How to put the ERD segments together to create
  the initial ERD

In this lecture you will practice
Next lecture you will learn
24
Database Initial Study

• Detailed description of organizations current
  and proposed database system environments
• Organizational
• Objectives (slide 4)
• Structure (slide 5)
• Operations (slide 6,7,8)
• Problems, Constraints (slide 9,10,11,12)
• System
• Objectives (slide 13,14,15)
• Scope and boundaries (slide 16,17,18,19)
• Information sources and users (slide 20,21)
• End-user requirements (slide 22,23)

25
UCL Database Initial Study

• About UCL
• Located in a central location on campus
• Accessible by all students
• Provides many resources (200 computers, laser
  printers, scanners, etc.)
• Provides service and support to faculty, staff
  and students

26
UCL Database Initial Study

• Users
• Assistant Dean of College of Business
• Computer Lab Director CLD (Labs operational
  management)
• Computer Lab Secretary - CLS
• (assists CLD in general administrative
  functions)
• Computer Lab Assistants - LA (Labs daily
  operations)
• Computer Lab Graduate Assistants GA
• (technical support and training to faculty and
  staff)
• Objectives
• Provide users with controlled access to UCL
  assets (computers, printers, supplies, software
  and documentations)
• Guide users working with assets and provide
  problem-solving services

27
UCL Database Initial Study

• Organizational Structure
• helps to define the organizations lines of
  communication and reporting requirements

28
UCL Operations

• Inventory / Storage / Order Management
• Equipment Maintenance and Repair Management
• Equipment Check-Out and Check-In Management
• Lab Assistant Payroll Management
• Lab Reservations Management
• Computer Lab Access Management

29
UCL Description of Operations

• Inventory / Storage / Order Management
• Classification of Items
• Hardware (computers, printers, etc.)
• Software (application programs)
• Literature (reference texts, software manuals)
• Supplies (consumables papers, printer ink
  cartridges)
• Inventory item classified by inventory type
  (group of all similar items)

Four level of hierarchy
30
UCL Description of Operations

• Equipment Maintenance and Repair Management
• Information kept in Bad Equipment Log and
  Hardware
• Returned for Service Log
• Equipment Check-out and Check-in Management
• Form completed when equipment checked out
• Notice sent for late equipment
• Lab Assistant Payroll Management
• Time sheets used to track assistants
• Hourly basis for a fourteen day period
• Lab Reservations Management
• Faculty member completes reservation form with
  date, time, department, course code and section
• Computer Lab Access Management
• Lab users sign users log and leave University ID
  card

31
Common Problems and Constraints in Current System

• Never up to date and yields errors
• Too much data duplication and inconsistency
• Does not generate useful information
• Does not allow ad hoc queries
• CLD spends too much time processing data manually
• Lack of computerization makes inventory
  management difficult

32
Specific Problems

• Inventory / Storage / Order Management
• No access to crucial inventory management data
  for CLD
• (which items have been ordered, from which
  vendor, which items have been ordered but have
  not yet been received)
• UCL needs available stock figures average use
  of supplies
• CLD does not know item location
• Equipment Maintenance and Repair Management
• CLD cannot generate repair and maintenance
  history for equipment
• CLD cannot determine the status of items subject
  to maintenance procedures

33
Specific Problems

• Equipment Check-out and Check-in Management
• CLD lacks information about lab assets
• Lab Assistant Payroll Management
• CLD spends too much time summarizing hours worked
  LAs
• CLD cannot estimate work loads
• Lab Reservations Management
• Manual reservation system inadequate
• No statistical information for scheduling lab
  reservations
• Computer Lab Access Management
• User log not properly maintained
• Certain items not returned
• Security problems

34
Constraints for UCL

• Time Frame
• Fully operational within 3 months
• Hardware and Software
• Use existing UCL hardware, software, and LAN
• Distributed Aspects and Expandability
• Operate in multiuser environment
• Independent of existing administrative systems
• Cost
• Development costs must be minimal
• Use no more than two additional terminals
• Operate without additional personnel
• 9,500 is available

35
Objectives for UCL

• General System Objectives
• Improve operational efficiency
• Provide useful information for planning, control,
  and security
• Specific Objectives
• Inventory/Storage/Order Management
• Better control of purchase orders
• Monitor stock of supply items
• Control inventory by type (group) and item
• Quick information about item location
• Timely information about use of supplies and
  generate statistics to aid in future purchases

36
Objectives for UCL

• Equipment Maintenance and Repair Management
• Monitor maintenance histories
• Track items returned to vendor
• Equipment Check-out and Check-in Management
• Tracks items checked out
• Monitor item check-out time
• Generate usage statistics
• Lab Assistant Payroll Management
• Scheduling and work loads
• Work summaries for each LA

37
Objectives for UCL

• Lab Reservations Management
• Decrease reservation processing time
• Produce reservation schedules
• Generate statistical summaries (by department,
  faculty, date...)
• Computer Lab Access Management
• Tigher control over users and lab resources
• Reduce sign-in time
• Provide peak use times information for scheduling
  purposes

38
Scope and Boundaries

• What will be systems extent?
• Covers only UCL portion of organizational chart
• Independent of other systems used on campus
• What operational areas are covered by system?
• Limited to six areas addressed earlier
• What design/implementation strategy to use?
• Organization into system modules.
• Accomplishes a specific system function, a
  component of the overall system such as
  Inventory, Purchasing, Sales, Accounting...
• What modules should be included in system?
• Lab management
• Inventory management
• How do modules interface?
• Through CHECK_OUT module

39
Modules and Interfaces
40
Modules

• INVENTORY process must permit
• Registering new inventory types and individual
  items
• Keeping track of an items location,
  classification and usage
• ORDER process
• Tracks types of inventory items that are ordered
  from vendors
• MAINTENANCE process
• Tracks both in-house and vendor repairs of items
  (has an interface with INVENTORY, because some
  items in inventory may have a repair history)
• CHECK-OUT process
• Tracks the checked out items by users (faculty,
  staff or student)

41
Modules

• ACCESS process
• Helps the CLD to track the Labs users
• (has an interface with CHECK_OUT, because some
  items are checked out by students, faculty and
  staff).
• RESERVATION process
• Tracks Lab reservations made by faculty or staff.
• (has interface with ACCESS, because faculty
  members reserve the Lab)
• (has interface with PERSONNEL, because an LA
  records the reservation)
• PERSONNEL process
• Facilitates the CLDs ability to monitor the LAs
  work schedules and actual hours worked.
• (has an interface with RESERVATION, because LAs
  record Lab reservations).

42
Database Design Phase Conceptual Design

• Gather information to identify the entities and
  describe their attributes and relationships.
• Information Sources and Users
• Confirmation of good information sources
• Confirmation process targets the current systems
  paper flow and documentation, including data and
  report forms.

43
(No Transcript)
44
Database Design Phase Conceptual Design

• Information Needs User Requirements
• (General Systems Requirements)
• Easy to use
• Provide security measures
• Fully integrated
• Able to access system concurrently
• Perform various functions
• Personnel, Inventory, Order, Maintenance,
  Reservation, Check_out, and Access
• Input requirements are driven by output
  requirements (desired query and reporting
  capabilities)

45
(No Transcript)