CSCI 4333 Database
Design and Implementation
Fall 2014
Instructor: Xiang Lian
Office:
ENGR 3.275
Web: http://faculty.utpa.edu/lianx/
Email: lianx@utpa.edu
Course:
CSCI/CMPE 4333 Database
Design and Implementation
Prerequisites:
CSCI/CMPE 3333 Algorithms and Data Structures
Time: TR, 9:10am ~ 10:25am.
Location: ACAS 1.104
Course Webpage: http://faculty.utpa.edu/lianx/old_courses/CSCI4333_2014fall.html
Instructor's office
hour: Monday and Wednesday
(2:30pm ~ 5:00pm); or by appointment
Teaching Assistant: Santiago Pazmino
Office: ENGR 3.273A
E-mail: sppazmino@broncs.utpa.edu
Phone: TBA
Office Hours: Tuesday and Thursday (1:00pm ~ 2:00pm); or by appointment
Textbook
Michael Kifer, Arthur Bernstein, and Philip M. Lewis, Database
Systems: An Application Oriented Approach, Compete Version, 2/E,
Addison-Wesley, 2005, ISBN 0321268458
Online Resources:
Description
This
course presents a comprehensive introduction to database management systems,
with the emphasis on database design methodologies (ER and/or UML modeling),
database query languages (relational algebra and SQL), database implementation
(physical data organization, indexing, query processing and optimization), and
database application development (JDBC/ODBC). The course is essential for one
to become a successful database architect, administrator, application
developer, or IT manager, and prepares students towards studying advanced
database topics, such as transaction processing, deductive databases,
distributed databases, and data warehouses. Students will (1) design a
real-life database to deepen their understanding of the database concepts and
theories, (2) implement their design in both Oracle and MySQL to become
proficient in the use of database management systems, and (3) develop a
database application that works with the two RDBMSs to obtain strong practical
skills in database programming. Students will be given an opportunity to
formally present their project in class. After taking this course, students
will have the capability of developing various database applications such as
enterprise information systems, e-commerce systems, business management
systems, etc. Topics include:
·
Modern
Database and Transaction Processing Systems
·
Relational
Data Model
·
Conceptual
Modeling of Databases with Entity-Relationship (ER) Diagrams and the Unified
Modeling Language (UML)
·
Relational
Algebra and SQL
·
Database
Design and Relational Normalization Theory
·
Triggers
and Active Databases
·
Using
SQL in an Application
·
Physical
Data Organization and Indexing
·
The
Basics of Query Processing
·
An
Overview of Query Optimization
·
Database
Tuning
·
Oracle
·
MySQL
·
XML
Databases (if time allowed)
·
RDF
Databases/Stores (if time allowed)
See
also the UTPA Catalog course description: CSCI/CMPE
4333 Database Design and Implementation.
Objectives
·
Become
familiar with the database technology.
·
Understand
the relational data model.
·
Learn
& apply conceptual data modeling techniques.
·
Become
familiar with the database design and normalization theory.
·
Master
relational algebra and Structured Query Language.
·
Become
familiar with database implementation issues.
·
Understand
database storage organization and query processing algorithms.
·
Learn
database indexing and tuning techniques.
·
Learn
administration and development in Oracle and MySQL.
·
Gain
practical experience in database and database application development.
·
Learn
and obtain skills on empirical evaluation of database systems; efficiency and
scalability.
·
Improve
technical writing and oral presentation skills.
Tentative Schedule
|
Week |
Topic |
Notes1 |
|
Week 1 (Aug. 26) |
|
|
|
Week 1 (Aug. 28) |
Assignment 1 (Due on Sept. 9) |
|
|
Week 2 (Sept. 2) |
|
|
|
Week 2 (Sept. 4) |
|
|
|
Week 3 (Sept. 9) |
|
Assignment
2 (Due on Sept. 23) |
|
Week 3 (Sept. 11) |
Conceptual
Modeling of Databases with Entity-Relationship (ER) Diagrams |
Project Part 1 (Due on Sept. 23) |
|
Week 4 (Sept. 16) |
|
|
|
Week 4 (Sept. 18) |
|
|
|
Week 5 (Sept. 23) |
|
Sept. 23: Last day to change to
non-credit Project Part 2 (Due on Oct. 7) |
|
Week 5 (Sept. 25) |
Q/A |
|
|
Week 6 (Sept. 30) |
EXAM I |
|
|
Week 6 (Oct. 2) |
|
|
|
Week 7 (Oct. 7) |
Project Part 3 (Due on Oct. 27) |
|
|
Week 7 (Oct. 9) |
|
|
|
Week 8 (Oct. 14) |
|
Assignment
3 (Due on Oct. 23) |
|
Week 8 (Oct. 16) |
|
|
|
Week 9 (Oct. 21) |
|
Reading
materials: Using SQL in an Application |
|
Week 9 (Oct. 23) |
||
|
Week 10 (Oct. 28) |
|
Project Part 4 (Bonus Project; Hard deadline:
Due on Nov. 20) |
|
Week 10 (Oct. 30) |
Q/A |
|
|
Week 11 (Nov. 4) |
EXAM II |
|
|
Week 11 (Nov. 6) |
|
|
|
Week 12 (Nov. 11) |
|
Assignment 4 (Due on Nov. 25) |
|
Week 12 (Nov. 13) |
|
|
|
Week 13 (Nov. 18) |
Online course evaluation (Wednesday, December 3rd at 11:59pm): https://my.utpa.edu |
|
|
Week 13 (Nov. 20) |
|
|
|
Week 14 (Nov. 25) |
|
Reading
materials: An Overview of Query Optimization |
|
Week 14 (Nov. 27) |
-- |
Reading
materials: Database Tuning Nov. 27-29: Thanksgiving Holiday |
|
Week 15 (Dec. 2) |
Q/A |
|
|
Week 15 (Dec. 4) |
-- |
Study days, No classes |
|
Week 16 (Dec. 6-12) |
Final Exam (8am - 9:45am, Dec. 11, Thursday) |
|
1 Academic calendar:
http://www.utpa.edu/calendar/academic-calendar/
Note: exam dates are tentative, exact dates
will be announced in class!!!
Drops
and drop passes must be handled by you and the admission office; I will sign
the necessary documents. But, I will not place a drop or drop pass on the final
grade sheet.
Study Group
Teresa
R. Garcia
Assistant
Director, Tutorial Services
Learning
Assistance Center
956-665-3072
terrieg@utpa.edu
Exam I .......…………………. 20%
Exam II.......…………………. 20%
Final Exam ………………… 25%
Assignments………………… 20%
Project
………………………
10%
Attendance
………………… 5%
A
= 90 or higher
B
= 80 - 89
C
= 70 - 79
D
= 60 - 69
F
= <60
Attendance in the lecture is mandatory. Students are responsible for all materials covered in class, the textbook, tutorials, and homework assignments. Students are expected to attend lectures, study the text, and contribute to discussions. You need to write your name on attendance sheets throughout the course, so please attend every lecture.
No make-up exams will be given except for university sanctioned excused absences. If you miss an exam (for a good reason), it is your responsibility to contact me before the exam, or soon after the exam as possible.
· Assignments must be submitted to Blackboard before class starts by the due date.
· An assignment turned in within one week after the due date will be considered late and will lose 30% of its grade.
· No assignment will be accepted for grading after one week late.
· Project deliverables cannot be submitted late without prior consent of the instructor.
The University expects a student to maintain a high standard of individual honor in his/her scholastic work. Unless otherwise required, each student is expected to complete his or her assignment individually and independently. Although study together is encouraged, the work handed in for grading by each student is expected to be his or her own. Any form of academic dishonesty will be strictly forbidden and will be punished to the maximum extent. Copying an assignment from another student in this class or obtaining a solution from some other source will lead to an automatic failure for this course and to a disciplinary action. Allowing another student to copy one's work will be treated as an act of academic dishonesty, leading to the same penalty as copying.
Students with
disabilities are encouraged to contact the Disability
Services office for a confidential discussion of their individual needs for
academic accommodation. It is the policy of the University of Texas-Pan
American to provide flexible and individualized accommodation to students with
documented disabilities that may affect their ability to fully participate in
course activities or to meet course requirements. To receive accommodation
services, students must be registered with the Disability Services office (DS),
University Center #108, 665-7005 or disabilityservices@utpa.edu.
·
Understanding of the database
technology.
o
Understand what
are databases, DBMSs, transactions, TPSs, OLAP, and OLTP.
o
Know the history and key players in
the modern database world.
o
Understand common database models,
including relational, object, object-relational, hierarchical, network, etc.
·
Understanding of the relational data
model.
o
Understand main concepts of
relational model: relations/tables, tuples/rows, attributes/columns, database and relation schemas.
o
Understand constraints: integrity,
key, foreign-key, syntactic, semantic, and reactive.
o
Have a basic understanding of data
management (schema creation, data population, querying, administrative
activities) using a relational database.
·
Proficiency with conceptual modeling
of databases using Entity-Relationship (ER) Diagrams.
o
Understand the E-R approach.
o
Understand entities and entity
types, relationships and relationship types.
o
Understand entity type hierarchies,
participation constraints, and part-of relationships.
o
Model enterprise data with ERDs.
o
Translate ERDs into relational
database schemas.
·
Familiarity with the database design
and normalization theory.
o
Understand the gap between
ERD-generated schemas and "good" database.
o
Understand the problems with
set-valued attributes, data redundancy, and various data anomalies.
o
Understand basics of the relational
normalization theory: decompositions, functional dependencies, and normal
forms.
·
Proficiency with relational algebra
and Structured Query Language (SQL).
o
Understand relational algebra and
construct queries using it.
o
Understand SQL and construct queries
using it.
o
Understand the relationship and
interactions of relational algebra and SQL.
·
Familiarity with database
implementation issues.
o
Understand the general architecture
of an RDBMS.
o
Understand the elements of a system
catalog.
o
Understand database physical storage
organization: heap and sorted files, main memory and disk interactions.
o
Understand indexes: clustered/unclustered, primary/secondary, sparse/dense, ISAM, B+
trees, hash indexes, etc.
·
Understanding of the basics of query
processing and query optimization.
o
Understand external sorting.
o
Understand evaluation methods for
projection, selection, and set operators.
o
Understand basic join algorithms:
nested-loops, sort-merge, and hash joins.
o
Understand the basics of query
execution planning, plan cost, and plan selection.
·
Familiarity with database tuning
techniques.
o
Understand basics of SQL DDL schema
tuning: indexes, denormalization, vertical and
horizontal partitioning, materialized views.
o
Understand basics of SQL DML: query
rewriting, eliminating sorting, allowing duplicates, minimizing communication.
·
Proficiency with the administration
and development in Oracle and MySQL.
o
Implement an enterprise database in
Oracle.
o
Implement an enterprise database in
MySQL.
·
Proficiency with database
application development.
o
Understand the basics of JDBC/ODBC.
o
Understand two and three tier
architectures for designing database applications.
o
Develop a GUI-based application with
querying capabilities for Oracle.
o
Develop a GUI-based application with
querying capabilities for MySQL.
·
Empirical evaluation of database
systems.
o
Understand the notions of efficiency
and scalability.
o
Test database performance.
·
Further improvement of technical
writing and oral presentation skills.
o
Write professional reports on
database design and implementation.
o
Present on database design and
implementation.
· (a) An ability to apply knowledge of computing and mathematics appropriate to the discipline.
· (b) An ability to analyze a problem, and identify and define the computing requirements appropriate to its solution.
· (c) An ability to design, implement, and evaluate a computer-based system, process, component, or program to meet desired needs.
· (d) An ability to function effectively on teams to accomplish a common goal.
· (f) An ability to communicate effectively with a range of audiences.
· (g) An ability to analyze the local and global impact of computing on individuals, organizations, and society.
· (h) Recognition of the need for and an ability to engage in continuing professional development.
· (i) An ability to use current techniques, skills, and tools necessary for computing practice.
· (j) An ability to apply mathematical foundations, algorithmic principles, and computer science theory in the modeling and design of computer-based systems in a way that demonstrates comprehension of the tradeoffs involved in design choices.
· (k) An ability to apply design and development principles in the construction of software systems of varying complexity.
The instructor reserves the right to alter this syllabus as necessary.