Database Systems: Design, Implementation, and Management

1
The Database Life Cycle (DBLC)
2
Completing the Conceptual and Logical Database
Designs
THE UNIVERSITY LAB CONCEPTUAL DESIGN
VERIFICATION, AND LOGICAL DESIGN

• Conceptual database design (initial E-R diagram)
  is in draft format, lacks the details.
• Use the initial E-R diagram as the basis for
  attribute definition, table normalization, and
  model verification, to see if the design meets
  processing and information requirements.
• These activities are often concurrent and
  iterative!
• E-R model verification represents a link between
  the database modeling and design activities,
  database implementation and database application
  design.

3
Completing the Conceptual and Logical Database
Designs

• Focus is on two modules
• Lab Management System Labs daily operations.
• targets the Labs users, the people who work in
  the Lab, and the scheduling of Lab resources.
• i.e. allows the director (CLD) to track the Labs
  resources by user type, department, etc.
• Inventory Management System in which the
  equipment, supplies, orders, and repairs are
  tracked.
• i.e. allows the CLD to track equipment that is
  temporarily checked out for use by faculty
  members and staff.

4
Completing the Conceptual and Logical Database
Designs
5
Completing the Conceptual and Logical Database
Designs

• Refine module definition
• Entities
• Attributes
• Normalization process
• Discover new entities
• Revise attributes

6
Completing the Conceptual Design Entities,
Attributes, and Normalization

• The Lab Management Modules E-R Segment

7
The Lab Management Module The USER
Entity (Dept Code is important for
contribution to the Labs budget)
8
Sample USER Data
USER_CLASS determines USER_TYPE. To eliminate
the 2NF, USER_TYPE and USER_CLASS could
be combined into a single attribute having the
values Stu/UG, Stu/GR, Fac, and Staff. But the
university requires a report that shows Lab
usage summaries by faculty, staff, and students.
9
The LOG Entity
Sample LOG Data

• How about adding an attribute such as LOG_ID?
• Is the attribute necessary or useful?
• If it is useful, what is the cost of creating and
  using it?
• What function does it have that can not be well
  served by other attributes?

10

• The LAB_ASSISTANT Entity
• CLD manages a group of lab assistants (LA).
• Graduate assistants (GA) 20 hrs work/week
• Student workers (SW) 10 hrs work/week
• Work study students (WS) - 4 hrs work/week

11
Sample LAB_ASSISTANT Data
12
The LAB_ASSISTANT Work Scheduling Sheet
13
The WORK_SCHEDULE Entity
Sample WORK_SCHEDULE Data
There can not be more than 4 lab assistants
assigned to work during the same time slot.
14
The HOURS_WORKED Entity
The HOURS_WORKED structure tracks the number of
hours worked by each LA during the two-week
period.
The HW_HOURS_WORKED attribute represents the
total hours worked by the LA during the pay
period and is entered manually by the end user.
(not a derived attribute). If the entity included
HW_TIME_IN and HW_TIME_OUT attributes,
the HW_HOURS_WORKED attribute values could have
been calculated from the other two time
attributes, and it would, in that case, have been
a derived attribute.
Sample HOURS_WORKED Data
15
The RESERVATION Entity
The Lab is used mostly by students doing their
assignments but, sections of the Lab may be
reserved by faculty members for teaching purposes
or by staff members for hardware and software
maintenance and updates. Multivalued attributes
cause problems. (A faculty member may reserve the
Lab for several dates and, within those dates,
several times per day, each time for a different
number of users).
16
The Revised RESERVATION Entity We can split the
RESERVATION structure into two tables in a 1M
relationship.
Each time an LA records a set of reservations,
the date on which the reservations are made is
recorded in RES_DATE. We can also track who
(USER_ID) made the reservation and who (LA_ID)
recorded it. The multiple occurrences of the
reservations are then handled by the many side
in a table named RES_SLOT.
Sample RESERVATION Data
17
The RES_SLOT Entity
Sample RES_SLOT Data
18
The Inventory Management Module
INV_TYPE makes it easy for the CLD to generate
detailed inventory summaries.
The INV_TYPE Classification Hierarchy
The INV_TYPE Classification Hierarchy as a Tree
Diagram
19
The INV_TYPE Entity
20
Sample INV_TYPE Data
21
The ITEM Entity
22
Sample ITEM Data
23
The LOCATION Entity
Sample LOCATION Data
24
The STORAGE Entity
Sample STORAGE Data
25
The REPAIR Entity
Sample REPAIR Data
26
The VENDOR Entity
Sample VENDOR Data
27
The ORDER Entity
Each order contains one or many items ordered.
The ORDER entity is related in a 1M relationship
with the ORDER_ITEM entity. The ORDER entity (the
1 side) contains general data about the order
the ORDER_ITEM entity (the M side) contains the
items in the order.
ORD_STATUS reflects reporting requirements. ORD_
FUND_TYPE lets us know to which budget we should
charge payments
Sample ORDER Data
28
The ORDER_ITEM Entity
Each ORDER references one or more ORDER_ITEM
records, but each ORDER_ITEM entry refers to a
single ORDER record.
29
Sample ORDER_ITEM Data
30
How to Keep Track of Items in Inventory
There are two different types of
transactions 1) Withdraw some items are
withdrawn (consumed) as they are used. These
items are decreased from inventory. 2)
Check-out some items are checked out
temporarily. These checked-out items remain in
inventory, but its availability status and
location change. We must be able to track each
itemss user and location. When the item is
returned, another (check-in) transaction changes
the availability status and location again.
1) Withdraw A USER can withdraw many ITEMs. An
ITEM may be withdrawn by many USERs.
N
M
31
The WITHDRAW Entity
To eliminate the multivalued attributes, we can
decompose the WITHDRAW entity into the two
entities.
32
The Revised WITHDRAW Entity
Sample WITHDRAW Data
Sample WD_ITEM Data
The WD_ITEM Entity
33
2) Check-out Check-out transactions are
subject to the same basic process as the
withdrawal transactions.
M
34
The CHECK_OUT Entity
Sample CHECK_OUT Data
Sample CHECK_OUT_ITEM Data
The CHECK_OUT_ITEM Entity
35
Logical Design

• When the conceptual design is completed, the ERD
  reflects the business rules that define the
  entities, relationships, optionalities,
  connectivities, cardinalities and constraints.
• Conceptual models entities must be normalized
  before they are implemented.
• Logical design translates conceptual model to
  format for selected DBMS
• Table structures are created, relationships are
  established

36
The STORAGE Table Structure Defined in Microsoft
Access
37
The COMPUTER LAB Database