Transcript DB Design - CS 457/557 : Database Management Systems

Database Design –
Chapter 10.1-10.2
Database design steps
1.
2.
3.
4.
5.
6.
Requirement collections and analysis
Conceptual database design
Choice of a DBMS
Data model mapping (logical design)
Physical DB design
DB system implementation
•
In-class database
1. Requirements collection & analysis
• What do you want from your DBMS?
– Major application areas and user groups identified
– Interviews, questionnaires collected
– Documentation analyzed
– Must identify objects in DB, characteristics of the
objects and the relationships among them
– Operating environment and planned use studied
– Functional requirements
• Description of typical operations to data
– E.g. queries, updates, etc., user-defined operations
Home Away From Home Realty
Company Objects
• Buildings, Apartments
• Renters?
– Corporate Clients
• Maintain?
– Manager, Staff members, inspectors
For Design
• What are the characteristics for each object?
• Buildings, Apartments, Renters (Corporate
Clients), Maintainance (Manager, Staff
members, inspectors)
What are the characteristics for each object?
Buildings, Apartments, Renters (Corporate Clients),
Maintainance (Manager, Staff members, inspectors)
Home Away From Home Realty
Company
• For each building: BuildingID (unique), and NoOfFloors (number of floors in
the building)
• For each apartment: AptNo (patially unique, i.e. unique within a building)
and NoOfBedrooms (number of bedrooms in the apartment)
• For each corporate client: CCID (unique), CCName (unique), CCLocation,
and CCIndustry
• For each manager: MgrID (unique), MgrFullName (composed of
MgrFname and MgrLName), multiple Phones, BDate, Age (derived from
Bdate and current date), Salary, and Bonus (not every manager will have a
bonus),
• For each staff member: SMemberID (unique) and SMemberName
• For each inspector: InsID (unique) and InsName
• What are the relationships among the
objects?
HAFH Requirements
• Each building has one or more apartments.
Each apartment is located in exactly one building.
• Each apartment is either leased to one corporate client or to
no one.
Each corporate client leases one or many apartments.
• Each corporate client can refer many corporate clients but
does not have to refer any.
Each corporate client can be referred by one corporate client
or is not referred by any.
• Each apartment is cleaned by either one or two staff
members.
Each staff member cleans between 5 and 10 apartments.
HAFH Requirements
• Each manager manages one or many buildings.
Each building is managed by exactly one manager.
• Each manager resides in exactly one building.
Each building either has one manager residing in it or no
managers residing in it.
• Each inspector inspects one or many buildings.
Each building is inspected by one or many inspector.
For each building that an inspector inspects, the date of last
inspection and the date of the next future appointment is
kept.
2. Conceptual DB design
• Conceptual schema design
– Big picture – data organization
• Provides: DBMS independent understanding
– Stable, tool for DB users
• Conceptual DB model represents conceptual schema
• Results are a diagram based on conceptual DB model,
e.g. ER, EER, UML diagram
Conceptual DB design cont’d
• Approaches to Conceptual Schema Design
– Top down, Bottom up, mixed
– One shot
• All requirements merged before design
• Reconcile differences
– View integrated
• Every user group define own schema
• Merged to provide global conceptual schema
• Requires data integration
– Identify and resolve:
» name – synonym, homonym
» Type – set vs. attributes
» Domain – char vs. int
» Constraints – key
– Merge views
– Remove redundancies
In Class DB Example?
Conceptual DB design cont’d
– Big picture – data usage
• Retrieval, update, mixed
• Ensures all info there
• Conceptual DB model used to represent conceptual
schema
• Results are a diagram based on conceptual DB
model, e.g. ER/EER, UML diagram
3. Choice of a DBMS
• What model and what software?
– Cost
• Software, maintenance, hardware, DB creation,
conversion, personnel, training, operations
– Other factors
• Familiarity, availability, philosophy of organization
– Features:
• Editors and browsers, report generators, tools used in
WWW, graphical DB design tools
Choice of a DBMS cont’d
– Data models
Relational, Object-oriented, object-relational,
noSQL
– DBMS based on data model
• Relational: Access, MySQL (open source)
• Object-Relational, Oracle, SQL-Server, DB2, Sybase,
PostgreSQL (open source)
• Object-oriented: ObjectStore, Versant (VSNT),
Objectivity/DB
• NoSQL: MongoDB Couchbase, SimpleDB
4. Data model mapping
• Creating the final blueprint
– Create conceptual schema and external schema in
data model
• System independent mapping (ER to relational)
• Tailoring schema to specific DBMS
5. Physical DB design
• Internal, low-level details
– Choose storage structures for files and access
paths
• Based on analysis of queries, transactions, etc.
• Indexing, clustering related records, hashing, etc.
– Response time
– Space
– Transaction throughput
6. DB system implementation
• Final steps of design process
– Create DDL for DB in DBMS
– Load the data into the DB