1 / 33

Review

Review. Hachim Haddouti. 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

jaunie
Download Presentation

Review

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. 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

More Related