Download
review n.
Skip this Video
Loading SlideShow in 5 Seconds..
Review PowerPoint Presentation

Review

210 Vues Download Presentation
Télécharger la présentation

Review

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Review Hachim Haddouti

  2. DB and File Systems • How modern databases evolved from files and file systems • File Systems vs DBMS • Structural and data dependence • What a database is, what it does, and why database design is important • What a DBMS is, what it does, and how it fits into the database system • About types of database systems • database models • Conceptual models: logical nature of data representation • Implementation models: emphasis on how the data are represented in the database (Hierarchical, Network, Relational) Hachim Haddouti and Rob & Coronel, Final Review

  3. Relational Model • relational database model as a logical view of data • relational database model’s basic components are entities and their attributes, and relationships among entities • How entities and their attributes are organized into tables • relational database operators, data dictionary • How data redundancy is handled in the relational database model • Why indexing is important Hachim Haddouti and Rob & Coronel, Final Review

  4. Entity Relationship (E-R) Modeling • conceptual model,internal, and external, and physical models (see next picture) • How relationships between entities are defined and refined, and how such relationships are incorporated into the database design process (multi value attribute, cardinality, connectivity, bridge or composite entity) • How ERD components affect database design and implementation • Other popular E-R modeling tools (Chen, Crow’s Foot) • DB Design concerns (Security, Performance, shared access, Integrity) Hachim Haddouti and Rob & Coronel, Final Review

  5. Data Models: Degrees of Data Abstraction Hachim Haddouti and Rob & Coronel, Final Review

  6. Normalization • Role of normalization in database design • Reduces data redundancies • Helps eliminate data anomalies • Produces controlled redundancies to link tables • About the normal forms 1NF, 2NF, 3NF, • That normalization and E-R modeling are used concurrently to produce a good database design • That some situations require denormalization to generate information efficiently Hachim Haddouti and Rob & Coronel, Final Review

  7. Normalization cont. Hachim Haddouti and Rob & Coronel, Final Review

  8. Example of ER Modeling • Create an ERD based on the Crow’s Foot model, using the following requirements. • ·An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice is written by a single sales representative. • ·The INVOICE is written for a single CUSTOMER. However, each customer may have many invoices. • ·An INVOICE may include many detail lines (LINE), which describe the products bought by the customer. • ·The product information is stored in a PRODUCT entity. • ·The product's vendor information is found in a VENDOR entity. Hachim Haddouti and Rob & Coronel, Final Review

  9. Example ERD Hachim Haddouti and Rob & Coronel, Final Review

  10. SQL • The basic commands and functions of SQL • How SQL is used for data manipulation (to add, modify, delete, and retrieve data) • How to use SQL to query a database to extract useful information • How SQL is used for data administration (to create tables, indexes, and views) • About more advanced SQL features such as updatable views, stored procedures, and triggers Hachim Haddouti and Rob & Coronel, Final Review

  11. SQL cont. • BETWEEN - defines limits • IS NULL - checks for nulls • LIKE - checks for similar string • IN - checks for value in a set • EXISTS - opposite of IS NULL Group Having Create View Hachim Haddouti and Rob & Coronel, Final Review

  12. SQL cont. • The SQL standard defines embeddings of SQL in a variety of programming languages such as Cobol, Pascal, Fortran, C, and Java. • EXEC SQL statement is used to identify embedded SQL request to the preprocessor EXEC SQL <embedded SQL statement > END-EXEC Note: this varies by language. E.g. the Java embedding uses # SQL {<embedded SQL statement > } ; • Dynamic SQL Hachim Haddouti and Rob & Coronel, Final Review

  13. SQL cont. • Trigger • Procedural SQL code invoked before or after data row is selected, inserted, or updated • Automate critical actions and provide warnings for remedial action • Stored Procedures • Named collection of procedural and SQL statements stored in database e.g. to represent multiple update transactions • ODBC/JDBC Hachim Haddouti and Rob & Coronel, Final Review

  14. DB Design • Information system and DB • Systems Development Life Cycle (SDLC) vs. • Database Life Cycle (DBLC) • How to conduct evaluation and revision within the SDLC and DBLC frameworks • What database design strategies exist: top-down vs. bottom-up design and centralized vs. decentralized design Hachim Haddouti and Rob & Coronel, Final Review

  15. DB Design - SDLC Hachim Haddouti and Rob & Coronel, Final Review

  16. DB Design - DBLC Hachim Haddouti and Rob & Coronel, Final Review

  17. DB Design cont. Hachim Haddouti and Rob & Coronel, Final Review

  18. DB Implementation ER Model Verification • Establishes • Design reflects end user views of database • Database transactions defined and modeled so design supports related requirements • Design meets output requirements • Design supports required input screens and data entry forms • Design flexible to support future enhancements • Verification identifies • Central entity • Each module and its components • Each module transaction requirement Hachim Haddouti and Rob & Coronel, Final Review

  19. Client Server Architecture • History • What client/server computing is • What the advantages of using client/server systems are • client/server architecture, system components and how they interact • What effect the client/server system has on the DBMS Hachim Haddouti and Rob & Coronel, Final Review

  20. Client Server Architecture cont. • Client • Front-end application • Server • Back-end application • Communications middleware • Communications layer • Thin/fat client • 2 tier, 3 tier , n tier architecture Hachim Haddouti and Rob & Coronel, Final Review

  21. Mainframe vs. C/S Hachim Haddouti and Rob & Coronel, Final Review

  22. Advantages of the Three-Tier Architecture • Heterogeneous systems • Tiers can be independently maintained, modified, and replaced • Thin clients • Only presentation layer at clients (web browsers) • Integrated data access • Several database systems can be handled transparently at the middle tier • Central management of connections • Scalability • Replication at middle tier permits scalability of business logic • Software development • Code for business logic is centralized • Interaction between tiers through well-defined APIs: Can reuse standard components at each tier Hachim Haddouti and Rob & Coronel, Final Review

  23. WebDB • HTML (GET, POST, FORM, INPUT), HTTP, CSS, XML, XSLT, JavaScript • Middle tier: CGI, application servers, Servlets, JSP, ASP, passing arguments, maintaining state (cookies) Hachim Haddouti and Rob & Coronel, Final Review

  24. WebDB cont. (Web Database Connectivity) • Common Gateway Interface (CGI) • Uses script files to connect to database • Perl, C++, VB, PHP • Application Programming Interface (API) • Newer and more efficient • Uses DLLs • Memory resident and faster • Well known APIs • Netscape (NSAPI) • Internet Server API from Microsoft (ISAPI) Hachim Haddouti and Rob & Coronel, Final Review

  25. Web-to-Database Middleware Hachim Haddouti and Rob & Coronel, Final Review

  26. WebDB cont. Web-to-Database Middleware Connection Methods • Native SQL access middleware • Use services of Open Database Connectivity (ODBC) • Use Object Linking and Embedding Database (OLE DB) interface with Windows • ODBC most common Hachim Haddouti and Rob & Coronel, Final Review

  27. WebDB cont. • Web app Server • Servlet (Code first, webpage second) • JSP (Webpage first, code second) • ASP • HTTP is stateless  Server-side state, Client-side state, Hidden state • Cookies • Issues of WebDB app (Data security, Transaction management, etc.) Hachim Haddouti and Rob & Coronel, Final Review

  28. DW • operational data vs. and decision support • data warehouse? • What star schemas are and how they are constructed • ROLAP, MOLAP • What data mining is and what role it plays in decision support Hachim Haddouti and Rob & Coronel, Final Review

  29. DW cont. • Decision support system? • Data Warehouse (Subject-oriented, integrated, time-variant, nonvolatile collection of data in support of management’s decision-making process” Inmon (AP = analytical processing is missing) • Data Marts (Single-subject data warehouse subset, Address local or departmental problems) • Separated DW data from operational environment • OLAP (Advanced data analysis environment) Hachim Haddouti and Rob & Coronel, Final Review

  30. DW cont. Hachim Haddouti and Rob & Coronel, Final Review

  31. DW cont. • ROLAP • MOLAP • Star Schema • Facts • Dimensions • Attributes • Attribute hierarchies • DW Operation (Drill Down, RollUp, Slice/Dice) • Data Mining Hachim Haddouti and Rob & Coronel, Final Review

  32. Organizational How do you assess yourself now? • Course Objective: • Upon sucessful completion ofthis course, a student will • 1. know strengths and weaknesses of the major conceptual and implementation models for databases • 2. know current trends in databases • Student will be able to • 3. produce good relational database design • 4. use SQL to build and query a database • 5. implement a database using a commercial DBMS Hachim Haddouti and Rob & Coronel, Final Review

  33. Organizational • Midterm exam 20 %, final exam 30 %, homework assignments 15 %, project 30 %, participation & attendance & punctuality 5 %. Hachim Haddouti and Rob & Coronel, Final Review