9 review hachim haddouti. 9 2 hachim haddouti and rob & coronel, final review how modern...

9

Review

Hachim Haddouti

2

9

Hachim Haddouti and Rob & Coronel, Final Review

• 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)

DB and File Systems

3

9


• 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

Relational Model

4

9


• 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)

Entity Relationship (E-R) Modeling

5

9


Data Models: Degrees of Data Abstraction

6

9


• 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

Normalization

7

9


Normalization cont.

8

9


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.

9

9


Example ERD

CUSTOMER

generates

INVOICE

INV_LINE

VENDOR

M

MM

1

1

1(1,1)

(1,1)

(0,N)

(0,N)(1,N)

contains

is written in

delivers

PRODUCT

(1,1)M 1

(0,N)(1,1)

EMPLOYEE

writes

(0,N)

(1,1)

1

M

10

9


• 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

SQL

11

9


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

GroupHavingCreate View

12

9


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

13

9


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

14

9


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

15

9


DB Design - SDLC

16

9


DB Design - DBLC

17

9


DB Design cont.

18

9


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

19

9


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

20

9


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

21

9


Mainframe vs. C/S

22

9


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

23

9


WebDB

• HTML (GET, POST, FORM, INPUT), HTTP, CSS, XML, XSLT, JavaScript

• Middle tier: CGI, application servers, Servlets, JSP, ASP, passing arguments, maintaining state (cookies)

24

9


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)

25

9


Web-to-Database Middleware

26

9


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

27

9


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

28

9


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

29

9


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)

30

9


DW cont.

31

9


DW cont.

• ROLAP• MOLAP• Star Schema

– Facts

– Dimensions

– Attributes

– Attribute hierarchies

• DW Operation (Drill Down, RollUp, Slice/Dice) • Data Mining

32

9


Organizational

How do you assess yourself now?

• Course Objective: • Upon sucessful completion of this 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

33

9


Organizational

• Midterm exam 20 %, final exam 30 %, homework assignments 15 %, project 30 %, participation & attendance & punctuality 5 %.

9 review hachim haddouti. 9 2 hachim haddouti and rob & coronel, final review how modern...

Documents

review hachim haddouti

final review data models

rob coronel

database hierarchical

final review normalization

data redundancies

good database design

data dictionary