Transcript Database Systems: Design, Implementation, and Management

9
Review
Hachim Haddouti
9
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
2
9
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
3
9
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
4
9
Data Models: Degrees of Data
Abstraction
Hachim Haddouti and Rob & Coronel, Final Review
5
9
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
6
9
Normalization cont.
Hachim Haddouti and Rob & Coronel, Final Review
7
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.
Hachim Haddouti and Rob & Coronel, Final Review
8
9
Example ERD
CUSTOMER
1
generates
EMPLOYEE
1
(0,N)
(0,N)
M
M
writes
(1,1)
1
VENDOR
INVOICE
(1,1)
1
(1,N)
(0,N)
contains
M
(1,1)
delivers
M
1
is written in
INV_LINE
(1,1)
Hachim Haddouti and Rob & Coronel, Final Review
(1,1)
M
PRODUCT
(0,N)
9
9
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
10
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
Group
Having
Create View
Hachim Haddouti and Rob & Coronel, Final Review
11
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
Hachim Haddouti and Rob & Coronel, Final Review
12
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
Hachim Haddouti and Rob & Coronel, Final Review
13
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
Hachim Haddouti and Rob & Coronel, Final Review
14
DB Design - SDLC
Hachim Haddouti and Rob & Coronel, Final Review
9
15
DB Design - DBLC
Hachim Haddouti and Rob & Coronel, Final Review
9
16
DB Design cont.
Hachim Haddouti and Rob & Coronel, Final Review
9
17
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
Hachim Haddouti and Rob & Coronel, Final Review
18
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
Hachim Haddouti and Rob & Coronel, Final Review
19
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
Hachim Haddouti and Rob & Coronel, Final Review
20
9
Mainframe vs. C/S
Hachim Haddouti and Rob & Coronel, Final Review
21
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
Hachim Haddouti and Rob & Coronel, Final Review
22
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)
Hachim Haddouti and Rob & Coronel, Final Review
23
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)
Hachim Haddouti and Rob & Coronel, Final Review
24
9
Web-to-Database Middleware
Hachim Haddouti and Rob & Coronel, Final Review
25
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
Hachim Haddouti and Rob & Coronel, Final Review
26
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.)
Hachim Haddouti and Rob & Coronel, Final Review
27
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
Hachim Haddouti and Rob & Coronel, Final Review
28
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)
Hachim Haddouti and Rob & Coronel, Final Review
29
DW cont.
Hachim Haddouti and Rob & Coronel, Final Review
9
30
9
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
31
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
Hachim Haddouti and Rob & Coronel, Final Review
32
9
Organizational
• Midterm exam 20 %, final exam 30 %, homework
assignments 15 %, project 30 %, participation &
attendance & punctuality 5 %.
Hachim Haddouti and Rob & Coronel, Final Review
33