Prerequisites: COSC 110 or permission of instructor
Study of database concepts. Detailed study of information concepts and the realization of those concepts using the relational data model. Practical experience gained designing and constructing data models and using SQL to interface to both multi-user DBMS packages and to desktop DBMS packages.
Course Objectives
- To apply knowledge of internal data structures and extend this knowledge to external data structures necessary for file management.
- To examine how data storage technology (hardware and access methods) works.
- To extend integrated file systems to the data base concept of data storage and retrieval.
- To define the terminology embodied in data base systems and that used with specific software packages.
- To develop an understanding of the relational data model theory.
- To develop an understanding of standard SQL in terms of relational data model theory using relational algebra or relational calculus.
- To study standard SQL and compare the standard with the SQL implementation available with existing DBMS software packages.
- To study the theory behind the relational approach to DBMS and the impact of this theory on data model design and validation (Normalization process).
- To gain practical programming experience with a multi-user relational DBMS package such as Oracle or SQL Server.
- To gain practical programming experience with a microcomputer based data base package such as Microsoft Access, Fox Pro, Paradox.
- To investigate client/server technologies as they pertain to databases e.g. ODBC.
Detailed Course Outline
1. Introduction to Database Concepts and Architecture. (3 hrs.)
Characteristics, advantages and implications of the database approach to information systems as contrasted with traditional integrated file systems. DBMS architecture including employing super data structures to implement relationships among records. The database system environment including data models, schemas, database languages, and interfaces.
2. Record Storage and Primary File Organizations. (3 hrs.)
Characteristics of secondary storage devices. Blocking and buffering as techniques to lessen the impact of physical I/O. The role of the operating system in secondary storage organization and access.
3. Trees and Indexed Access Methods. (3 hrs.)
A quick review of trees. Introduction of the B+Tree as the basis of indexed sequential access methods. Insertion, deletion, and retrieval using the B+Tree. Indexes on multiple keys.
4. Information Science Concepts and Data Modeling Using Entity Relationship Diagrams. (4 hrs.)
Information analysis to identify query keys, candidate keys, entities, attributes, relationships, and integrity constraints. ER modeling as a means of representing information concepts. Extended entity relationship modeling as it relates to specialization, generalization and inheritance.
5. Exam 1 (1 hr.)
6. The Relational Data Model and Relational Algebra. (6 hrs.)
Relational model concepts. Definition and use of the eight relational algebra operations to query a relational database. Defining a relational schema using an ER diagram.
7. SQL The Relational Database Standard. (4 hrs.)
Use of SQL to define a relational data model. SQL as an implementation of the relational algebra operations. Basic and complex queries in SQL. Insert, delete, and update statements in SQL. Defining and using Views in SQL.
8. Exam 2 (1 hr.)
9. Practical Experience with a DBMS Product that Implements the Relational Approach and SQL. (7 hrs.)
In-depth introduction to an existing DBMS package that implements the relational approach and SQL such as ORACLE, MS SQL Server, or VaxRdbSql. Students acting as DBAs will create a data model using the SQL language. Again, using the SQL language, students will insert, update, and delete data values from the database. Students will then implement complex queries to retrieve data from the database.
10. Normalization as a Process for Verification of Data Model Design (3 hrs.)
Definition of functional dependency, full functional dependency, transitive dependency, and multivalued dependency. Definition of the normal forms from unnormalized through fourth normal form and how to apply the normalization process to recognize normal forms. How to move a data model to a higher normal form and the issues of denormalization as it applies to retrieval performance.
11. SQL Interaction with Third Generation Programming Languages. (4 hrs)
Methods for extending data base functionality to third generation programming languages such as Cobol. Description of precompliers, subroutine calls, module level language, and dynamic SQL. Students will implement a program written in a third generation programming language, which provides insertion, deletion, updating, and query capabilities to a database.
12. Desktop DataBase Packages. (3 hrs)
Presentation of a desktop database package as an implementation of a relational DBMS such as MS Access, Fox Pro, or Paradox. Students will construct a data model and database using the package. Students will then implement numerous queries using the IDE of the package.
Evaluation Methods
The final grade is determined as follows:
Exam 1 and 2 — 200 points
Final — 100 points
Homework — 100 points
SQL Online Project — 50 points
SQL Embedded Project — 50 points
Micro DBMS Project — 50 points
Total: 550 points
The lines for the course grades are as follows:
Total Possible Points
100–90% = A
89–80% = B
79–70% = C
69–60% = D
59–0% = F
Suggested Assignments and Projects:
Homework 1: Given Application Statements Design Entity-Relationship Diagram (1 week)
Students are supplied with a five- to six-paragraph description of an application and they then identify components of a data model (entity, attributes, relationships, and constraints) as present in the description. Students then represent the data model using an ER diagram. Discussions develop concerning what is an entity vs an attribute. Attributes of relationships are also encountered and how these are to be implemented depending on the cardinality of the relationships. Extended ER diagram components such as superclass and subclass also are discovered.
Homework2: Given the Cobol Source Code Describing the File Environment for an Integrated File Application Design an Entity-Relationship Diagram (1 week)
The record descriptions (in the form of Cobol source code) of an integrated file application are provided to the students. They then identify components of a data model as present in this source code. Students then represent the data model using an ER diagram. Naming conventions in the Cobol source mislead students and they have to rely on the functional dependencies to determine good design. Discussions develop concerning why tables are embedded within records and how many to many relationships are implemented in integrated file systems.
Homework3: Given a Set of Queries Write the Relational Algebra Needed to Provide Answers (1 week)
Students use the 8 relational algebra operations to answer a series of complex queries. Students realize that many alternative solutions are possible.
Homework4: For the Same Set of Queries Write Standard SQL Queries (1 week)
Students learn how the eight relational algebra operations are present in standard SQL while learning alternative methods of answering queries.
Project1a: Using a Non-Desktop DBMS Implement the Data Model (1 week)
Using a mainframe or client/server database construct the domains, tables, and views necessary to implement a data model. Syntax of the DBMS is compared to that described in the SQL Standard. The data model will contain attributes, various data types, domains, constraints, and other clauses. Students then show the metadata present in the data model.
Project1b: Using a Non-Desktop DBMS Insert Data Values into the Tables (1 week)
Various methods of placing values into the base tables are discussed including import utilities, SQL Insert statements, stored procedures, and writing a custom program. Discussions of data types and problems with integrity constraints occur. The importance of “Commit” and “Rollback” are emphasized. Select statements are used to demonstrate the correct contents of the tables.
Project1c: Using a Non-Desktop DBMS Implement SQL Query Solutions (1 week)
Students enter the SQL Select statements from Homework 4 and determine if the answers are correct.
Project2: Using SQL with Third Generation Languages (2 weeks)
Students write an interactive menu-driven program, which uses SQL to interact with their database. The SQL mechanisms needed such as cursors are discussed and implemented. The mechanisms used to allow a 3gl programming language to interact with a DBMS are also discussed. These include embedded SQL using a precompiler, a call level interface or API, dynamic SQL, and module level language. Students are required to answer several queries some preplanned some parameterized. One query requires the use of a cursor. The program also requires update, delete, and insert operations with logic to deal with DBMS objections to violations or integrity constraints.
Homework5: Apply the Normalization Process to a Data Model (1 week)
Given a data model, apply the normalization process to validate the design.
Project3: Implement the Data Base Using a Desk Top DBMS (1 week)
Students utilize a GUI interface to define a data model. Data values are then imported from fixed width text files into the base tables. Numerous discussions result concerning data type mismatches. Students then use the GUI to implement queries. The differences between the DBMS and the relational data model and the SQL standard are discussed.
Required textbooks, supplemental books and readings
Codd, E. F., "Relational Database: A Practical Foundation for Productivity", Communications of the ACM, Vol. 25, No. 2, February, 1982, pp. 109- 117.
Elmasri, R., Navathe, S., Fundamentals of Database Systems, Third Edition, Addison-Wesley, 2000.
Kent, William, "A Simple Guide to Five Normal Forms in Relational Database Theory", Communications of the ACM, Vol. 26, No. 1, February 1983, pp. 120-124.
