CSBP430 – Database Systems Chapter 16: Practical Database Design and Tuning

1. CSBP430 – Database SystemsChapter 16: Practical Database Design and Tuning Elarbi Badidi College of Information Technology United Arab Emirates University ebadidi@uaeu.ac.ae

2. The Information System • Database • Carefully designed and constructed repository of facts • Part of an information system • Information System • Provides data collection, storage, and retrieval • Facilitates data transformation • Components include: • People • Hardware • Software • Database(s) • Application programs • Procedures

3. Information System Life Cycle System Development Life Cycle (Macro Life Cycle) • Feasibility analysis: This phase is concerned with analyzing potential application areas, identifying the economics of information gathering and dissemination, performing preliminary cost-benefit studies, and determining the complexity of data and processes. • Requirements collection and analysis: Detailed requirements are collected by interacting with potential users and user groups to identify their particular problems and needs. • Design: This phase has two aspects: the design of the database system, and the design of the application systems (programs) that use and process the database. • Implementation: The information system is implemented, the database is loaded, and the database transactions are implemented and tested. • Validation and acceptance testing: The system is tested against performance criteria and behavior specifications. • Deployment, operation and maintenance: The operational phase starts when all system functions are operational and have been validated. Monitoring of system performance and system maintenance are important activities during the operational phase.

4. Database Lifecycle (DBLC) Micro Life Cycle

5. Information Flow Diagram 2b Tasks 3b Abstract Code w/SQL 4b 3GL Code w/SQL 1 2a 3a 4a ER Diagram Relational Schema Relational Platform Overview of the Methodology Analysis Specification Design Implementation 1 2 3 4

6. Phase 1:Database Initial Study (Requirements Collection and Analysis)

7. Purposes • Analyze company situation • Operating environment • Organizational structure • Define problems and constraints • Analyze and study existing documentation concerning the application (forms, reports, policy manual). • Analyze the expectations of the users • Determine system requirements • Define objectives • Define scope and boundaries • Analyze flow of information

8. Analysis • Input: • descriptions of documents and tasks; scenarios; usage statistics; plans for the future system; relevant laws, constraints, and policies • Output: • Information Flow Diagram (IFD) modeling, external documents, internal documents, tasks, and system boundary. • Techniques: • interviews with people at all levels of the enterprise • analysis of documents, scenarios, tasks • reviews of short and long-term plans, manuals, files, and forms • abstraction • Tools: • Information Flow Diagrams

9. D2 D3 D4 D1 T1 Database T2 T3 T4 D6 D5 task name information flow document name system boundary Information Flow Diagram

10. Inquiry Reservation/ Cancellation Ticket Boarding Pass Flight Schedule Make Reservation/ Cancellation Answer Inquiry Passenger list Enter Flight Schedule DB Assign Planes Enter Airports Create Flight Inst Enter Planes Assign Planes Airports Create Flight Inst Airplanes Example: Information Flow Diagram Check-In Process Check-in

11. Phase 2: Database Design

12. Database Design • Most Critical DBLC phase • Makes sure final product meets requirements • Focus on data requirements • Subphases • Create conceptual database design • Create logical database design (Data Model Mapping) • Create physical database design • DBMS software selection

13. Database Design (Con’t.) • Purpose: • create detailed design of normalized relational database schema • create detailed design of tasks using abstract code with embedded SQL • identify need for views • Input: • Analysis Report, IFD. • Output: • relational schema w/primary and foreign keys, constraint definitions in SQL, abstract code w/SQL, view definitions • Techniques: • database normalization; abstract coding • Tools: • mapping: ER-Model  Relational Model • abstract code; SQL; views

14. Database Design (Con’t.)

15. I. Conceptual Database Design • Conceptual schema design: • Data modeling creates abstract data structure to represent real-world items • High level of abstraction • Three steps • Data analysis and requirements • Entity relationship modeling and normalization • Data model verification • transaction design: • Design the database transaction • Retrieval transaction, update transaction, mixed transaction

16. I. Conceptual Database Design Data analysis and Requirements • Focus on: • Information needs • Information users • Information sources • Information constitution • Data sources • Developing and gathering end-user data views • Direct observation of current system • Interfacing with systems design group • Business rules

17. I. Conceptual Database Design Entity Relationship Modeling and Normalization

18. Airport Code Name City Airport State I. Conceptual Database Design Example ER-Diagram Airports Airport Code Name City State - - - - - - - -

19. Example ER-Diagram Dtime Atime Airline Airport Code From Name Miles 1 n City Airport Flt Schedule n 1 To Price State Flt# Weekday 1 Instance Of Plane# Plane Type Date n Assigned 1 n Ticket# Flt Instance Airplane n Seat# Total #Seats #Avail Seats Reser- Vation Street Check-In Status n First City Customer Address Customer Name Customer Middle State Phone# Cust# Last Zip

20. E-R Modeling is Iterative

21. I. Conceptual Database Design DB Design Strategy Notes • Top-down (Fig 16.2 page 540) • 1) Identify data sets • 2) Define data elements • Bottom-up (Fig 16.3 page 541) • 1) Identify data elements • 2) Group them into data sets

22. I. Conceptual Database Design Top-Down vs. Bottom-Up

23. II. DBMS Software Selection • DBMS software selection is critical • Advantages and disadvantages need study • Factors affecting purchasing decision • Cost (software, maintenance, hardware, training, etc) • DBMS features and tools • Underlying model • Portability • DBMS hardware requirements

24. III. Logical Design (Data Model Mapping) • Translates conceptual design into internal model • Maps objects in model to specific DBMS constructs • Design components • Tables • Indexes • Views • Transactions • Access authorities • Others

25. IV. Physical Design • Choose specific storage structure and access paths • Very technical • More important in older hierarchical and network models • Indexing, clustering of related records on disk, linking related records via pointers. • Designers favor software that hides physical details

26. Phase 3:Implementation and Loading

27. Implementation and Loading • Creation of special storage-related constructs to house end-user tables • Data loaded into tables • Other issues • Performance • Security • Backup and recovery • Integrity • Company standards • Concurrency controls

28. Implementation • Input: • relational schema w/primary and foreign keys, data representation, constraints in SQL, abstract code w/SQL, task decompositions, view definitions • Output: • conceptual schema, internal schema, host-language code w/embedded SQL • Tools: • SQL, host-language • relational database management system, pre-compiler • host-language compiler

29. Example Conceptual Schema Implementation CREATE DOMAIN AIRPORT-CODE CHAR(3) CREATE DOMAIN FLIGHTNUMBER CHAR(5); CREATE DOMAIN WEEKDAY CHAR(2) CONSTRAINT DAYS CHECK ( VALUE IN (‘MO’,’TU’,’WE’,’TH’,’FR’,’SA’,’SU’)); CREATE TABLE FLT-SCHEDULE (FLT# FLIGHTNUMBER NOT NULL, AIRLINE VARCHAR(25), DTIME TIME, FROM-AIRPORTCODE AIRPORT-CODE, ATIME TIME, TO-AIRPORTCODE AIRPORT-CODE, MILES SMALLINT, PRICE DECIMAL(7,2), PRIMARY KEY (FLT#), FOREIGN KEY (FROM-AIRPORTCODE) REFERENCES AIRPORT(AIRPORTCODE), FOREIGN KEY (TO_AIRPORTCODE) REFERENCES AIRPORT(AIRPORTCODE));

30. Example Conceptual Schema Implementation CREATE TABLE FLT-WEEKDAY (FLT# FLIGHTNUMBER NOT NULL, WEEKDAY WEEKDAY, UNIQUE(FLT#, WEEKDAY), FOREIGN KEY (FLT#) REFERENCES FLT-SCHEDULE(FLT#)); CREATE TABLE FLT-INSTANCE (FLT# FLIGHTNUMBER NOT NULL, DATE DATE NOT NULL, PLANE# INTEGER, PRIMARY KEY(FLT#, DATE), FOREIGN KEY FLT# REFERENCES FLT-SCHEDULE(FLT#), FOREIGN KEY PLANE# REFERENCES AIRPLANE(PLANE#));

31. Example Task Implementation some C code Direct-Flights T1.1 /* read(Inquiry, :Departure-Airport, :Arrival-Airport,:Date); */ /* convert :Date to :Weekday; */ more C code EXEC SQL WHENEVER NOT FOUND GOTO endloop; more C code EXEC SQL DECLARE DIRECT-FLIGHTS CURSOR FOR SELECT FROM-AIRPORTCODE, TO-AIRPORTCODE, FLT-SCHEDULE.FLT#, DTIME, ATIME FROM FLT-SCHEDULE, FLT-WEEKDAY WHERE FLT-SCHEDULE.FLT#=FLT-WEEKDAY.FLT# AND FROM-AIRPORTCODE=:Departure-Airport AND TO-AIRPORTCODE=:Arrival-Airport AND WEEKDAY=:Weekday ORDER BY DTIME; more C code EXEC SQL OPEN DIRECT-FLIGHTS; while EXEC SQL FETCH DIRECT-FLIGHTS INTO :From, :To, :Flt#, :Dtime, :Atime; write(Inquiry, :From, :To, :Flt#, :Date, :Dtime, :Atime) endwhile; more C code endloop: Exec SQL CLOSE DIRECT-FLIGHTS;

32. Phase 4: Testing and Evaluation

33. Testing and Evaluation • Database is tested and fine-tuned for performance, integrity, concurrent access, and security constraints • Done in parallel with application programming • Actions taken if tests fail • Fine-tuning based on the DBMS reference manuals • Modification of physical design • Modification of logical design • Upgrade or change DBMS software or hardware

34. Phase 5:Operation

35. Operation • Database considered operational • Starts process of system evaluation • Unforeseen problems may surface • Demand for change is constant

36. Phase 6: Maintenance and Evaluation

37. Maintenance and Evaluation • Preventative maintenance • Corrective maintenance • Adaptive maintenance • Assignment of access permissions • Generation of database access statistics to monitor performance • Periodic security audits based on system-generated statistics • Periodic system usage-summaries