Transcript No Slide Title

MIS 4233/62333
Enterprise Systems
The University of Tulsa
Professor: Akhilesh Bajaj
Large Scale Requirements Modeling
All Slides in this presentation © Akhilesh Bajaj, 2008. All Rights Reserved.
What is a Data Model?
• A way to structure information fields in the business
• We want to make sure the same information is not repeated for data entry
• In a business information system, there is data entry (WRITE)
and data querying (reports), also termed READ.
• While many people can reuse information once it is entered, in reports, (i.e. many
READS), we don’t really want duplicate/redundant data entry
Why?
• A data model at the design level allows us to structure the fields so they are entered
only once during data entry. This eliminates duplicate work and removes potential
for error.
• We don’t design reports when we develop a data model schema; instead, we only
care about the data entry part.
• We will learn a method to structure our fields: DSD.
Data Screen Model
• We need to create: screens for data entry, each of which has screen elements
Screen:
-Screen Title
-Automatic buttons: Find, Create, Update, Delete, Reset, Exit
CUSTOMER
Find
Create
Update
Reset
Delete
EXIT
Example Screen with Title: Customer
Data Screen Model
Screen Elements: Belongs to a screen, and is
-either a textbox with label, or
-drop-down list with label
-Optional: a primary key designator (asterisk)
CUSTOMER
*
Customer_ID
Customer_name
Find
Reset
Create
Update
Delete
EXIT
Example Screen with Title: Customer PATTERN 1: Objects/Events
Data Screen Model
Primary Key: Used to identify an object amongst its own group (like customers)
Or events within its own group (like sales transactions, where each sale gets its own id).
Pattern 1: Objects/Events
Usually, we identify groups of objects and events, and allocate an ID for each.
CUSTOMER
*
Customer_ID
Customer_name
Find
Create
Update
Reset
Delete
EXIT
Example Screen with Title: Customer
Data Screen Model
On each screen, there are a set of elements that would make up the primary key of that
screen. First identify what we are creating on that screen, then decide on the PK!
If the screen lists the objects or events (like a customers or a sales transactions screen),
then the ID of that object or event is usually the primary key element.
Pattern 2: Links
Some screens link objects with other objects (for example, customers with sales).
The primary key of such a screen would be the IDs of both customers and sales.
Not names, because names are not assumed as primary keys
(more than 1 customer may have the same name)
CustomerSale
*
Customer_ID
Sale_ID
*
CustomerSale_Date
Find
Reset
Create
Update
Delete
EXIT
PATTERN 2 Example Screen with Title: CustomersSales. Note the primary key.
Data Screen Model
Basic Rules:
1. The only attributes that can be a drop_down_list on the current screen
are those that make up the complete primary key on at least one screen.
This could be another screen.
2. All labels on a screen must be prefixed with the screen_title, or the screen_title of
another screen where the element(s) make up the complete primary key, followed by a
_.
3. The only attribute(s) that can be repeated from another screen are the ones that
make up the entire primary key of that screen. When they are repeated, they
have to be drop_down lists.
4. Sample Data: For each field, put in a sample data value: one sample value if it is a text
box and two sample values if it is a drop down list.
Data Screen Model Examples
-If an object or event has many values for a single attribute (like a customer may have
many email values at the same time for customer_email attribute) it gets a separate
screen.
EmpEmail
Employee_Id
*
EmpEmail_value
*
Find
Create
Update Delete
Reset
EXIT
-PATTERN 3: MultiValued Fields
Note how Employee_ID comes from the Employee screen.
-The only attributes that are repeated on another screen are the entire primary key
from that screen.
Fun Task: Identify The Patterns
EMPLOYEE
Employee_Id
PROJECT
*
Employee_Name
Project_Descriptn
Employee_Id
Find
Create
*
Project_ID
Update Delete
Find
Reset
Create
Update Delete
Reset
EXIT
EmpEmail
EmpWorksOnProj
Employee_Id
*
EmpEmail_value
*
Find
Create
EXIT
Update Delete
Reset
Employee_Id
*
*
Project_ID
EmpWorksOnProj_dateBegan
EmpWorksOnProj_dateEnded
Find
Create
Update Delete
Reset
EXIT
EXIT
Data Screen Model Examples
PATTERN 4: Subsets
Sometimes, a subset of the objects that would usually be managed on a screen have
extra attributes. In that case, we can make an extra screen for these specialized attributes
The object or event will still be identified by its primary ID.
MANAGER
EMPLOYEE
*
Employee_Id
*
Employee_Id
Employee_Name
Manager_ College
Employee_Id
Manager_Highest
_degree
Find
Create
Update Delete
Find
Create
Update Delete
Reset
Reset
EXIT
EXIT
-Note how Employee_ID comes from the Employee screen.
-The only attributes that are repeated on the second screen are the entire primary key
from that screen.
-An employee who is a manager will be edited using both screens. Could we have
put all fields on just the Employee Screen?
Data Screen Model Examples
PATTERN 5: Existence
-Sometimes, objects or events need their own ID plus another object/event’s
ID to be uniquely identified. In this case the primary key of the object will have multiple
fields.
Course_Section
Course
Course_Id
*
Course_Section_Id
Course_Name
Course_Desc
Create
*
*
Course_Section_
Date_Began
Course_Cred
Find
Course_Id
Classroom_ID
Update Delete
Find
Create
Update Delete
Reset
Reset
EXIT
EXIT
-Note how Course_Section is uniquely identified by two keys. This means that
Course section ids are unique within a course, but not across courses.
Course_section will only be edited using its screen.
Example: MIS4233 is the course _ID and Fall2010A may be the course_ section_ID.
There are many course sections in the University with course_section_ID =
Fall2010A. Can we think of some other examples of this situation?
Basic Thumb Rules
• On any screen, look at the object/event/link/ being
managed (created/updated/deleted). We cannot put a
field on that screen if there are multiple values in that
field for an instance of whatever we are managing on
that screen.
• If we use the pattern of extra attributes on a separate
screen with the primary key borrowed from one
screen, we need at least two extra attributes on that
screen.
Data Screen Model Example
University Schema:
Objects: Courses, Course sections, Professors, Students (graduate and undergraduate)
, Classrooms, buildings
Example course numbers: MIS3053, MIS4233, MIS3023.
Example course section identifiers: MIS3053Fall2008A, MIS4233Fall2008A
Example StudentID: 0918512
Example FacultyID: 0918452
Example BuildingID: HELM, OLIP
Example classroom ID: HELM316
Events: A student takes a course section and gets a grade, a professor teaches a
course section and gets a rating for that course section, a graduate student may TA a
Course section, and also get a rating for it.
Example grade: ‘A’
Example Professor rating: “Excellent’
Example GA rating: ‘Excellent’
Let us build a DSD schema for this description.
In Class Assignment 1
Al’s motor shop (AMS) is an automobile repair facility owned
by the Capone family. AMS has 5 repair bays. Each repair bay
(place where car is repaired) has a bay_id and bay_location.
AMS employs 14 employees. Each employee has an
employee_id, address, phone and salary. Of these 14, 2 are
office staff. They are further described by typing_speed and
degree_held. The 12 mechanics are further described by
tech_level. Each mechanic is assigned to work on one bay.
AMS customers have a cust_id, name, address, phone. In
addition, Mr. Capone also wants to capture information
for each customer that lists the mechanic who last did a job
for the customer, the date on which the job was done, and
the amount the customer paid to AMS.
• Please capture the above requirements in a DSD diagram.
In Class Assignment 2
Wimpy runs a burger joint. He has 6 employees working for him.
Some of these are shift managers, the others are grade 1 workers.
Wimpy wants you to capture information on each shift. A shift is
8 hours long. Each shift needs a shift manager and 0 or more
grade 1 workers. Being a purist, Wimpy sells only burgers, and
only 3 types of burgers at that: the wimpy mini, the wimpy burger
and the wimpy super. Each burger type has a price, a recipe and
amount of fat calories. In each shift, burgers of all 3 types can be
sold. Each shift has a time began and a time ended. Wimpy wants
to capture information on how many burgers were sold
(of each type) in a shift.
• Please capture the above requirements in a DSD diagram.
Tables Construction & Foreign Keys
Employees (emp_id, phone, address, salary)
Mechanics (emp_id, tech_level)
Tables are different
from screens but
Office_staff (emp_id, typing_speed, degree_held) can be mapped
emp_id FK REF employees
from them.
They contain the
Repair_bays (bay_id, bay_loc)
Customers (cust_id, name, address, phone)
data at the
“back end”. Screens
Last_job (cust_id, emp_id, date, payment)
are the “front end”.
cust_id FK REF customers, emp_id FK refs mechanics
The Foreign Keys (FK)
Works (emp_id, bay_id)
are the same as
emp_id FK REF mechanics, bay_id FK REF repair_bays
“borrowed” attributes in
the DSD schema.
emp_id FK REF employees