CS377 Syllabus & Progress
CS377 Syllabus & Progress
To save an example from a webpage, do the following in Netscape:
Click File -> Save As
Complete the file name in the "Selection" box
(Make sure you specify the right directory name and file name !)
Click OK when the file name is right
- Managing Information using Computers:
- Managing Data/Information:
click here
- Storing information on hard disks:
click here
- Accessing data files on hard disks:
click here
- How to read all data stored in a file:
click here
- Managing Data using "traditional" files --- Physical Data Dependence
- Record formats and data files:
click here
- Traditional file processing illustrated with an example:
click here
- Problems with traditional file processing:
click here
- Physical Data dependence:
click here
- Intro Database System --- Physical Data Independence
click here
- Breaking the Physical Data Dependence:
- Meta Data:
click here
- Using the meta data to
decouple a program from the data format:
click here
- Reading the
meta data information
click here
- How to store the data if we
do not know its format:
click here
- Reading the data file using
its meta data information
click here
- Advanced example: look up data using the first field in record:
click here
- Summary:
Physical Data Independence
click here
- Logical Data Independence:
the format of the stored data
vs. the format of the presented data:
- Intro:
click here
- Naive method to map stored format to presentation format:
click here
- Separating the way to store the data and the way to present the data:
click here
- Logical Data Independence:
click here
- Software Architecture of a Database Management System:
click here
- Database Terminology:
click here
Hand out Project 1: click here
- Data modeling using the Entity-Relationship model (Chapter 3)
- Introduction to the ER model (Entities and Relationships):
- Entities:
- Relationships:
- Prelude to Relationships - Mathematical relations:
click here
- Terminology: click here
- Properties of relationships:
click here
- Attributes of relationships:
click here
- Design Example Continued:
- The relationships in the Company ER-model:
click here
- Analyzing the relationships in the Company ER-model:
click here
- Weak (identifying) relationships:
click here
- ER-diagrams:
click here
- Information represented in an ER-diagram:
click here
- Accuracy of the data model (what does a relationship mean ?):
click here
Hand out Homework 2: click here
- The Relational Data model (Chapter 5 in book)
- Introduction:
- Designing a relational model from an ER-model - part 1, the entities:
click here
- Designing a relational model from an ER-model - part 2, the relationships:
- How to represent relationships in a Relational Model:
click here
- Representing weak relationships:
click here
- Representing other relationships:
click here
- Representing ternary relationships in Relational Model:
click here
- Looking ahead:
Defining the tables of a Relational Model:
click here
Hand out Homework 3: click here
- Relational Algebra
- Introduction to data manipulation:
click here
- Introduction to Relational Algebra:
click here
- (Mathematical) Set operations:
click here
- Database specific operations:
- The selection (σ) and
the projection (π) operations:
click here
- The (inner) join ⋈ operation:
click here
- The other inner join operations
(equi-join, theta-join and natural join):
click here
- Extension to the (inner) join -- the outer join:
click here
- Sample queries using Relational Algebra:
click here
- Efficiency considerations:
click here
- The Set Division operation:
- Set functions:
click here
- Forming groups and using Set Functions on groups:
- Forming groups and applying a set function on individual groups:
click here
- Forming groups using multiple attributes:
click here
- Applying multiple set functions on groups:
click here
- Formal notation, giving names to set function results
and forming a single group:
click here
- Practicing queries in Relational Algebra:
click here
- Not any and only type of queries:
click here
- *** My "systematic" query formulation summary in
Relational Algebra --
for reference only, not discussed in class:
click here
Hand out Homework 4: click here
- SQL - Structured Query Language - Data Definition
- Introduction:
click here
- Lectures use examples in
/home/cs377001/demo/Data-Definition
- Defining a database (= defining relations in a database):
- Defining a database and its users:
click here
- Defining relations (inside a database):
click here
- Defining constraints on relations:
- Removing/Changing an existing relation:
- SQL - Structured Query Language - Query formulation
- Accessing the class databases using the cs377-sql tool:
click here
- Query formulation - part 1: tuple selection (tuple condition)
- Introduction to the SELECT command:
- Qualifying attributes, aliasing and
renaming output attributes:
click here
- The tuple conditions in SQL:
- Intro:
click here
- The "easier-to-explain" tuple-conditions
(bool expr, IS [NOT] NULL, [NOT] LIKE, [NOT] IN):
click here
- The "harder-to-explain" tuple-conditions
(ANY, ALL, EXISTS):
click here
- Summary of the tuple-conditions in SQL:
click here
- Advanced query formulation --- Nested queries:
- Correlated nested queries:
- Simple technique ("trick") to find other attributes with
a foreign key using a nested query:
click here
- Advanced query formulation techniques:
- Set operations (∪, ∩, −,...) in SQL:
click here
- Techniques to find
the intersection and difference:
click here
- Using set difference to test subset/superset conditions:
click here
Hand out SQL project 1: click here
Midterm covers
material upto this point
Further SQL query features:
- Temporary relations:
click here
- The join operators of SQL-99:
- Miscelaneous Features of SQL (loose ends, including sorting):
click here
Three value logic used in SQL:
click here
- Query (select) formulation - part 2: group selection (group condition)
- Set functions:
- Forming groups based on grouping attributes and
conditions on a group:
- Forming groups that have common attribute values:
- Conditions on a group:
- Closer look at the grouping condition:
click here
- Nesting set-functions:
click here
- Doing grouping SQL queries using a
sub-query:
click here
- A systematic query formulation in SQL:
click here
- Using count(distinct ...) set function to solve
subset/superset queries:
click here ---
unnecessary material (cover
if we have extra time)
Hand out SQL project 2: click here
- Insert, Delete and Update commands:
click here
- Brief intro to transaction processing (using MySQL):
click here
- Views (Logical Tables) and
Logical Data Independence:
click here
JDBC: a Java library class for accessing Relational DBMS
- Java Database Connection (JDBC):
- Intro to client/server programming:
click here
- Intro to JDBC:
click here
- Coding the steps in query processing:
- Loading the JDBC driver using the DeviceManager class:
click here
- Making a connection to the SQL server:
click here
- Reserving buffer (create a SQL statement object) to execute queries:
click here
- Submitting a query for processing:
click here
- Retrieving the result tuples:
click here
- Clean up when the program is done:
click here
- Compiling and running
CS377 JDBC programs:
click here
- Example: select * from employee
click here
- Error handling in JDBC:
click here
- Some functions for manipulating the result set:
click here
- Accessing the Meta data in JDBC:
click here
- Using Meta data in JDBC:
click here
- My tiny MySQL client JDBC program:
click here
- External SQL material:
Java GUI programming
- JDBC makes it possible to develop database applications
that runs inside a Webbrowser (i.e., Java applets)
- Applets are graphical applications and you need to
learn Java GUI programming to write Java applet
- Intro to Java graphical programming:
click here
- Containers: JFrame and JPanel:
- "Normal" components: labels, buttons, etc
- Layout - Placement of graphical components:
- Event-driven programming - Making components active:
- Some examples in Java Graphical programming:
- Example 1: compute the average of a list of numbers:
click here
- Example 2: copy the text from input window to output window:
click here
- Writing Java Applets (skipped):
click here
Hand out JDBC project:
click here
Web Access of Dababases
- Intro to the World Wide Web:
- Intro to PHP programming:
- PHP program structure, comments and file inclusion:
click here
- Types and variables in PHP:
- Operators and statements:
click here
- Output (print and printf):
click here
- Special structures in PHP:
- Functions in PHP:
- Defining and using (calling) a function:
click here
- Scoping rules (of variables inside a function):
click here
- Parameter passing --- by value and by reference:
click here
- Accessing an mySQL database using PHP:
- Stand-alone PHP program that accesses the database:
- Web server executing a PHP program that accesses the
database:
(use: public_html/Web/)
- Executing a PHP program directly by the
web server:
- Executing a PHP program through
a HTML form:
- Passing parameters to a web-server:
click here
- Using the GET method to execute SQL queries:
click here
- Using the POST method to execute SQL queries:
click here
- A webpage to access companyDB database:
click here
- External: PHP Manual
click here
Hand out PHP project !!!
Database Design using decomposition to 3NF/BCNF
- Intro:
click here
- The first normal form (1NF):
click here
- Functional Dependencies:
- Inference Rules:
- Preliminary to finding keys of a relation ---
finding the closure set of a set of attributes:
- Intro: identifying keys of a relation R:
click here
- The closure set X+ of a set of attributes X:
click here
- An algorithm for finding
the closure set X+ of
X:
- Finding all keys of a relation:
- The theory of "decomposing a relation":
- Intro to the 3NF - how to detect "bad" relations:
click here
- The effect of decomposing a relation:
click here
- Condition to obtain a lossless decomposition:
click here
- Database design using decomposition into 3NF:
- A concrete example of database design using decomposition into 3NF:
click here
- An abstract example of database design using decomposition
into 3NF:
click here
- The Boyce-Codd Normal Form (BCNF):
- Wrap up on normalization:
click here
Database Design using the synthesis algorithm to the 3NF
- Decompositions that preserves
functional dependencies:
click here
- Minimal Cover of a set of FDs:
- Cover of a set of functional dependencies:
click here
- Equivalence of 2 sets of functional dependencies:
click here
- Minimal cover of a set of functional dependencies:
click here
- The synthesis algorithm for
dependency preserving decomposition:
click here
Transaction Processing --- if time permits....
- Introduction:
click here
- Implementing transactions with in-place updates (Undo logs):
click here
- Implementing transactions with deferred updates (Redo logs):
click here
- Concurrentcy Control:
click here
Under construction...
Concurrency Control (Chapter 18)
Recovery (Chapter 19)
Oracle Pro*C embedded SQL environment:
click here