Professor Harry
Wechsler
Department of
Computer Science
e-mail : wechsler@cs.gmu.edu
www: http://cs.gmu.edu/~wechsler/
(703)993-1533
(office)
(703)993-1530
(sec)
(703)993-1710 (fax)
FALL '2003
CS 450 --- Database Concepts
Class
Information
002 00922 R
Prerequisites
Grade
of C or better in MATH 203 and CS 310.
Each
student enrolled in this class certifies that he / she
has the prerequisites listed above.
Office Hours
R
Teaching
Assistants
1. Noorullah Moghul nmoghul@gmu.edu
SITE II / rm. 365 /
phone #: 703 – 993 - 1544
Office Hours: Monday
2. Jifei Xia jxia@gmu.edu grades the homework
SITE II / rm. 365
/ phone#
703 – 993 – 1544
Office Hours: Wednesday
Textbook
1. Database
Systems – The Complete Book – Updated Version by Hector Garcia-Molina,
Jeffrey Ullman and Jennifer
Widom, Prentice Hall, 2002.
The book’s home page is: http://www-db.stanford.edu/~ullman/dscb.html
Slides for the
lectures are available at:
http://www-db.stanford.edu/~ullman/dscb/gslides/er/index.html
http://www-db.stanford.edu/~ullman/dscb/gslides/er-rel/Slide1.html
:
:
2. ORACLE 9i
Programming --- A Primer, by Rajshekhar Sunderraman, Addison Wesley, 2004.
(Oracle 8 will be used because Oracle
9i could not be installed in time.)
On-line
ORACLE links and documents
To get an ORACLE account contact : oracle@ite.gmu.edu
ORACLE primer:
http://ite.gmu.edu/machines/oracle.html
Course
Description
Introductory course -- covers from basic to intermediate knowledge for the
design, implementation
and use of database systems. The main topics
include the Entity-Relationship (ER) and
Entity-Enhanced Relationship (EER) models and database design, Relational
Algebra (RA),
Structured Query Language (SQL), functional dependencies and normalization of a
relational database.
The course concludes with brief
introduction to object-oriented and object-relational databases,
data warehousing and data mining.
Students practice to design, develop and implement
a relational ORACLE database and
to use the database for transaction processing, report generation, and
queries.
Grading
1.
Homeworks #1 - # 6 à 50 %.
(10
points / day penalty for late homeworks)
Homeworks (HW) and Term Project
Students are provided with the requirements for building a database system.
The term project is
implemented stepwise as part of regular homework assignments. Students have to
design the database (HW#1),
create the database and load the system using ORACLE (HW #3), write SQL
statements to implement queries
and views (HW#4), and use embedded SQL to generate a report to support a
decision-making system (HW#6).
HW#2 and HW#5 are related to Relational Algebra and functional normalization,
respectively.
2.
MIDTERM à EXAM 1: Thursday
10/16 == 25 %
3. EXAM 2: Thursday 12/11 == 25 %
Honor
Code
Honor Code procedures will be strictly adhered. Students are required to be
familiar with the honor code.
You must not utilize unauthorized material or consultation in responding to
your tests.
Violations of the honor code will be reported. Unless otherwise stated,
homework assignments
must be based on the student’s own effort.
Please be sure that you are aware of all provisions of the GMU Honor Code
http://jiju.gmu.edu/catalog/apolicies/honor.html
http://www.cs.gmu.edu/honor-code.
Tentative
Schedule
|
August 28 |
|
|
September 4 - 11 |
|
|
September 18 - 25 |
|
|
October 2 - 9 |
|
|
October 16 |
EXAM 1 covers ER design including inheritance and (min, max) notation and its mapping to relations |
|
October 23 |
|
|
October 30 |
|
|
November 6 |
Normalization; Ch. 8 : System Aspects of SQL (Embedded SQL) |
|
November 13 |
Embedded SQL; Sect. 8.7 Security and User Authorization in SQL |
|
November 20 |
|
|
December 4 |
|
|
December 11 |
EXAM 2 (covers 9/18 – 11/13 lectures) |
HOMEWORK #1 - Due on September 25
Consider the following set of data requirements for the BANKING ENTERPRISE DATABASE SYSTEM (BEDS):
(a) The bank is organized into regions (NE, midAtlantic, South, MidWest, Pacific NorthWest and CA) and branches.
Each branch is located in a particular city and it is identified by a unique name. The bank monitors the assets of
each branch.
(b) Bank customers, which include both individual (people) and companies, are identified by their customer-id values.
The bank stores the details on each customer to include name, full address, email address, and phone and fax numbers.
Customers may have accounts and can take out loans. A customer may be associated with a particular banker,
who may act as a loan officer or personal banker for that customer.
© Bank employees are identified by their employee – id values. The bank keeps information on the name and
(full address, email, phone and fax numbers) for each employee, the names and age for her dependents, and
the employee-id value for the employee’s manager. Information is also kept for each employee on her job type
and grade, start date and salary.
(d) Both accounts (savings, CD, and checking) and loans, which are unique and identified as such, can be held
by more than one customer, and a customer can have more than one account or loan. The bank maintains details on
the interest rate, which can be fixed or variable, the balance, and the day the account or loan was opened. For loans
the bank maintains information on loan payments, their due date, and payment record. For accounts the bank maintains
information on the checks paid and the date when this was done.
-----------------------
Draw the E / R diagram for the above BANKING ENTERPRISE DATABASE SYSTEM (BEDS)
enterprise database system. Please remember to specify key attributes for each entity type and relationship,
to use (min, max) notation to indicate the type of participation and the cardinality
corresponding to structural constraints on each relationship type, and to take advantage of
the IS-A inheritance concept.
HOMEWORK #2 - Due on October 16
(a) Map the E/R diagram of the BANKING
ENTERPRISE DATABASE SYSTEM (BEDS)
into a Relational Database System. (see Sect. 3.1 – 3.3)
(NEW b) Solve Exercises 5.2.1 (ONLY a through e) (page 207), 5.2.4 (ONLY a through f) (page 210), 5.4.1 (page 230),
5.5.1 (a and b) (page 235) from the
textbook.
(ORIGINAL b) Solve Exercises 5.2.1 (page 207), 5.2.4 (page 210), 5.4.1 (page 230),
5.5.1 (a and b) (page 235) from the textbook.
NOTE: If you solved the original “b” assignment and submitted it on the original due date 10-9
you could earn up to 100 extra points !
HOMEWORK #3 - Due on October 23
Create the BANKING ENTERPRISE DATABASE SYSTEM (BEDS) using ORACLE and populate ('load') the database,
with at least 15 records for each table. Your report should include the relational database model
and a printout of both your session (DDL statements) on ORACLE and the contents of the loaded
(populated) database system.
HOMEWORK #4 - Due on November
13
1. Query and Modify the BANKING ENTERPRISE DATABASE SYSTEM (BEDS) database using SQL. Include several (>3)
retrieval of information (select-from-where), set operations, join operations, aggregate functions, nested subqueries,
and views. The modification of the database includes several (>3) deletions, insertions, and updates.
2. Update the database system to include integrity constraints of your own choice.
Implement several integrity constraints using CONSTRAINT, CHECK, referential integrity
(on delete/update cascade), and TRIGGERS statements. Run several SQL queries to validate
the usefulness of each of the above integrity constraints you have just implemented; show what happen if the
Integrity constraints are violated.
HOMEWORK #5 - Due on November 20
1. Solve Exercises 3.4.1 (page 88), 3.4.2 (page 89), 3.5.1 (page 100), 3.6.1 (page 117) from the textbook.
2. Normalize the relational BANKING ENTERPRISE DATABASE SYSTEM (BEDS) design
to the BCNF level and update the database from Homework #3 accordingly –THIS IS PAPER DESIGN ONLY–
you don’t have to redo the database using ORACLE.
HOMEWORK #6 - Due on December 4
Generate reports of your own choice using PL/SQL, (Pro*C or Pro*C++) Embedded SQL, JDBC, for the BANKING ENTERPRISE DATABASE SYSTEM (BEDS).