Database Design Chapter 3 1 Database design steps

Database Design – Chapter 3. 1

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), Maintenance (Manager, Staff members, inspectors))

Home Away From Home Realty Company • For each building: Building. ID (unique), and No. Of. Floors (number of floors in the building) • For each apartment: Apt. No (patially unique, i. e. unique within a building) and No. Of. Bedrooms (number of bedrooms in the apartment) • For each corporate client: CCID (unique), CCName (unique), CCLocation, and CCIndustry • For each manager: Mgr. ID (unique), Mgr. Full. Name (composed of Mgr. Fname and Mgr. LName), multiple Phones, BDate, Age (derived from Bdate and current date), Salary, and Bonus (not every manager will have a bonus), • For each staff member: SMember. ID (unique) and SMember. Name • For each inspector: Ins. ID (unique) and Ins. Name

• 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 (2 different words same thing), homonym (2 words sound same different thing) » Type/domain – set vs. attributes, char vs. int » Constraints – key – Merge views – Remove redundancies

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, no. SQL – DBMS based on data model • Relational: Access, My. SQL (open source) • Object-Relational: Oracle, SQL-Server, DB 2, Sybase, Postgre. SQL (open source) • Object-oriented: Object. Store, Versant (VSNT), Objectivity/DB • No. SQL: Mongo. DB, Couchbase, Neo 4 j

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

CS 100 and 101 Lab TAs • What? – CS 100 and 101 need TAs to help students in lab • When? – CS 100: Tuesdays and/or Wednesdays 2: 00 -3: 50 – CS 101: Wednesdays 2: 00 -3: 50 • Why? – $10/hour • How? – Email Maclane May | may@cs. ua. edu