1 / 37

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

CSBP430 – Database Systems Chapter 16: Practical Database Design and Tuning. Elarbi Badidi College of Information Technology United Arab Emirates University ebadidi@uaeu.ac.ae. The Information System. Database Carefully designed and constructed repository of facts

sailor
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related