Download A Database Design Methodology - DataBase Management - Report and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
A Database Design Methodology
Area of application:
Design of database with its applications.
Perspective:
The method assumes that the primary purpose of the future system is to automate current or planned activities of the enterprise. The method assumes (as do all database design methodologies) that dif- ferent views on the enterprise, conflicts, and political differences will be resolved during the database design process.
Life-Cycle:
The life-cycle comprises the following steps: Project Progress Report: Phase I g I.1. Environment & Requirement Analysis g I.2. System Analysis & Specification Project Progress Report: Phase II g II.1. Conceptual Modeling g II.2. Logical Modeling g II.3. Task Emulation g II.4. Optimization (NOT REQUIRED for the 424 project) Project Progress Report: Phase III g III. Implementation g III.1 Convert Emulated tasks to code g III.2 Bulk-Loading & Tuning (LIMITED for the 424 project) g III.3 Testing
Limitation:
The methodology does not cover implementation, testing, maintenance, and project management.
Example
Design a Merryland State Motor Vehicle Administration (MVA) Informa- tion System
The system must support all activities of the Merryland State MVA.
The tasks of the MVA include maintaining all information pertaining to: vehicle emissions, license maintenance , vehicle registration maintenance, fine and fee maintenance, driving record maintenance and report genera- tion.
Etc., etc., ...
Guidelines: g Techniques: collect information by contacting interviews of people at all levels of the organization; analyze questionnaires; review short and long term plans, business manuals, files, forms, etc. Tools: express a top-level information flow diagram to capture the func- tions and important documents of the enterprise, and to start the design with the i/o documents and work from the outside in towards a "top-level" design.
The tool we use for designing the top-level information flow diagram is the following graphic formalism for representing structures and processes :
structure
process
information flow
g Two structures are never directly connected.
g Two processes are never directly connected.
Personal
PEI Information
DRT Driving Record Maintenance
Reports
RPT
LMT License Maintenance
ETT Emmissions Testing
FFT Fine and Fee Maintenance
RMT Registration Maintenance
Information
Owner
Information
Vehicle
Information
Payment
Information
Unpaid Fine
Information
Driver License
Information
Driver Record Schedule
Violation
Schedule
Fee
OWI
VHI
PRI
UFI
DLI
VSI DRI
FSI
Warning
Points Report
Driving Record Citation
Violation Request
Driving Record Request
Violat. Change
VCE RQE DRE CTE PWE
Statistics
Yearly
Accounting
Monthly
Bad Records
Monthly
License
Driver’s
Notice
Lic. Renewal
Application
License
Test Notice
Emmisions
ETE
LAE
LRE
LIE
MBE
MAE
YSE
VQE Vehicle Report Request
FCE
FPE
LPE
FSE
TSE
RCE
RRE
RAE
VRE Vehicle Report
Registration Application
Reg. Renewal Notice
Registration Card
Tag and Sticker
Fee Schedule Report
Late Payment Notice
Fine Payment
Fee Change Request
MVA Top-Level Information Flow Diagram
Examples of Task Forms
TASK NUMBER: LMT
TASK NAME: License Maintenance PERFORMER: Driver registration staff PURPOSE: (^) Maintain all driver licensing information
ENABLING COND: (^) Receipt of License Application (LAE); Receipt of
License Renewal Notice (LRE); First day of each month; Last day of each month; DESCRIPTION: (^) Issue new licenses and process renewals. Print
renewal notices on a monthly basis. Delete expired licenses on a monthly basis. FREQUENCY: See subtasks (different for each subtask!) DURATION: See subtasks (different for each subtask!) IMPORTANCE: See subtasks (different for each subtask!) MAXIMUM DELAY: See subtasks (different for each subtask!) INPUT: (^) (DLI) Driver License Infor; (FSI) Fee Schedule;
(LAE) License Application; (LRE) License Renewal Notice; (PEI) Person Info OUTPUT: (^) (DLI) Driver License Info; (ENE) Expiration No-
tice; (LIE) Driver’s License; (LRE) License Renewal Notice; (PEI) Person Info; (PRI) Payment Record DOCUMENT USE: (^) (DLI) all columns; (LIE) all columns; (LAE) all
columns; (PEI) all columns; (FSI) all columns; (PRI) all columns; (LRE) all columns; (ENE) all columns OPS PERFORMED: See subtasks SUBTASKS: (^) (LMT.1) NEW-LICENSE; (LMT.2) RENEW-
LICENSE; (LMT.3) PRINT-RENEWAL- NOTICES; (LMT.4) DELETE-EXPIRATIONS ERROR COND: See subtasks
Examples of Task Forms
TASK NUMBER: LMT.
TASK NAME: NEW-LICENSE
PERFORMER: Driver Registration Staff PURPOSE: (^) Issue a license to a previously unlicensed driver, or
to a driver whose license has expired. ENABLING COND: Receipt of License Application (LAE) DESCRIPTION: (^) Checks if the applicant has unpaid fines or untested
cars; if not, records the information pertaining to the applicant and prints a driver’s license FREQUENCY: 300,000 per year DURATION: Short IMPORTANCE: Medium/high MAXIMUM DELAY: 10 minutes INPUT: (^) (LAE) License Application; (FSI) Fee Schedule;
(PEI) Person Info; (DLI) Driver License Info OUTPUT: (^) (LIE) Driver’s License; (DLI) Driver License Info;
(PEI) Person Info DOCUMENT USE: (^) (DLI) all columns; (LIE) all columns; (LAE) all
columns; (PEI) all columns; (FSI) all columns OPS PERFORMED: (^) (1) If no PEI tuple exists where PEI.SSN =
LAE.SSN, skip to step (2). Otherwise, if PEI.FINE-TEST-STATUS = NOT-OK, stop (person has unpaid fines or untested cars). Oth- erwise, delete the tuple from PEI. (A new one with the info from LAE will be created - this al- lows a person to change his/her address and/or name).
Rule of Thumb for Task Decomposition
g Many performers are required to carry out the task and each performer has different skills, or each can carry out a part independently.
g Different levels of authorization exist for carrying out different parts of the task.
g Different enabling conditions activate parts of the task.
g Different frequencies and durations apply to different parts of the task.
g Input documents are not used uniformly within the task.
g Different documents are used for different parts of the task.
g Many diversified operations are carried out within the task.
g Many subtasks are controlled by the task.
Examples of Task Forms
TASK NUMBER: LMT.1.
TASK NAME: ISSUE-LICENSE
PERFORMER: Driver Registration Staff PURPOSE: (^) Print a license to be issued to a driver (either a new
license or a renewal). ENABLING COND: Activated by LMT.1 or LMT. DESCRIPTION: (^) Collect driver information for the specified driver
FREQUENCY: 1,050,000 per year DURATION: Very short IMPORTANCE: Medium/high MAXIMUM DELAY: 10 minutes INPUT: (^) (DLI) Driver License Info; (PEI) Person Info
OUTPUT: (^) (LIE) Driver’s License
DOCUMENT USE: (^) (LIE) all columns; (PEI) all columns; (DLI)
DRIVER-ID, EXPIRATION-DATE, LICENSE- CLASS OPS PERFORMED: (^) (1) Join DLI.DRIVER-ID to PEI.SSN; select those
tuples where DLI.DRIVER-ID = DRIVER-ID (input parameter). This will select one tuple, since DLI.DRIVER-ID is the key. (2) Copy all relevant retrieved information to a new LIE (i.e., print a driver’s license using the infor- mation) SUBTASKS: none ERROR COND: (^) No DLI/PEI tuple exists for the given
LIC.DRIVER-ID.
Field
TASK
I
I
I
I
I
SSN I
Name
Height
Weight
Sex
Birthdate
Address Fine-Test- Status
PEI LMT.1.1 LMT.1.2 LMT.2 LMT.3 LMT.
I
I I
I
I
I
I
I
I
I
I/O
I/O
I/O
I/O
I/O
I/O
I/O
I/O
LMT.
Driver-ID
Expiration-
Issue-
Class
License-
Date
Date License- Status
Field
TASK DLI LMT.1 LMT.1.1 LMT.1.2 LMT.2 LMT.3 LMT.
I/O
O
O
O
O
I
I
I
I
I
I I
I
Example of Task-Data Usage Matrices
= external document
= internal document
= task
Example of Task Flow Diagram
clock clock
LRE Lic. Renewal Notice PEI Person-Info
License-Info
Driver-
DLI
Application
License-
LAE Schedule
Fee-
FSI
Record
Payment
PRI
License Maintenance Task
Notice
Expiration-
ENE
License
LIE Driver’s-
LMT. Delete- Expirations Notices
Print-Renewal-
LMT.
License
Issue-
LMT.1.
License
New-
LMT. License
Renew-
LMT.
Payment
Record-License-
LMT.1.
II.2. Logical Modeling
The purpose of this phase is to map the conceptual schema of the database to a logical data model that is supported by (directly implementable on) a DBMS. We will use the relational model as a logical data model.
Input:
The E-R diagrams and Functional Dependencies discovered by the 1-1, 1-many, and other constraints imposed by the semantic of the application.
Output:
A Logical Schema (relational in our case).
Function:
Map the E-R model to tables, their keys, and FDs. Then normalize the relations to obtain as close to BCNF relations.
Guidelines:
g Technique: use the E-R to table mapping technique and the key algo- rithms.
Normalization !!!
g Make sure all domains are atomic (1. NF)
g Identify all Functional Dependencies (FDs)
g Decompose relations to remove nonfull FDs (2. NF)
g Decompose relations to remove transitive FDs (3. NF)
g Make sure all determinants are candidate keys (BCNF)
II.3. Task Emulation
The purpose of this phase is to obtain the design and specification of the software that performs the tasks before any database implementation starts. In other words, before creating a schema in the DBMS, the application pro- gramming is fully specified. This gives the opportunity to correct the logical schema when it is incomplete, superfluous, or even dead wrong. Doing the design of the database schema and the applications using it simultaneously complements these two orthogonal specifications and catches most of the errors before the implementation.
Input:
The Logical Schema from the previous phase and the Task Forms.
Output:
g The set of design specifications of the pieces of software that performs the tasks described in the task forms. The design specifications can be given in terms of abstract programs with embedded sequences of DML state- ments, using the DML of the data model. g External Schemata based on the Logical Access Path Schema. g Internal Schema constituting an optimized Logical Access Path Schema.
Function:
g Use the Task Forms describing the tasks. Formulate for each task an abstract program including embedded sequences of DML statements that perform the task using the conceptual schema. (During this phase small corrections of the conceptual schema may be needed to support the tasks: validation ). g Design the Logical Access Paths for all queries. Construct the Task-Query Frequency and Probability Matrices. Integrate the Query Graphs into the Logical Access Path Schema. g Group Conceptual Schema relations and Views (nodes in the Logical Access Path Schema) used by a task or a set of similar tasks into External Schemata. Where applicable, replace sequences of embedded DML state- ments in the task specifications by statements accessing the views in the External Schemata. g Design the Internal Schema based on the Logical Access Path Schema (by introducing indices for the most frequently use Access Paths).
Guidelines:
g Techniques: those that apply to the use of the particular DML.
g The tool are the DML, the graphic notation for Access Paths, the Task- Query Frequency and Probability Matrices, and the Internal Schema Definition Language (i-DDL).
