CSCI/CMPE 4333 Database Design and Implementation
|
Department of Computer Science
University of Texas - Pan American
FALL 2009
|
- 11/12: Assignment 6 is out.
- 11/12: Project Stage 3 and Project Stage 4 are out. Note that, to get a grade, you should schedule a 10-15 min demo with the TA before the deadline. You should contact Eric in advance to check his availability.
- 10/29: Project Stage 2 is out.
- 10/15: MID-TERM EXAM is scheduled on 10/27 (Tuesday). The exam covers Chapters 1-5 of the textbook, with the main focus on ER modeling, relational algebra, and SQL. To prepare for the exam, I recommend to: (1) revisit your project stage 1 document, (2) revisit the in-class handout with relational algebra and SQL examples, (3) review this sample midterm exam paper with solutions, and (4) revisit Assignments 1-5.
- 10/15: Assignment 5 is out.
- 10/06: Assignment 4 is out.
- 09/24: Project Stage 1 is out.
- 09/22: Assignment 3 is out.
- 09/15: Assignment 2 is out.
- 09/14: Note the new office hours of the TA.
- 09/03: Assignment 1 is out.
- 08/27: All students who would like to take the course are expected to attend the first class.
MySQL: The world's most popular open source database
Oracle: the #1 relational database (48.9% market share in 2008)
1 The project can be done individually or in a team of at most three students. Please read Team Work Policy.
Grades are available via the blackboard.
| Instructor: | Dr. Artem Chebotko |
| Office: | ENGR 3.2100 |
| Web: | http://www.cs.panam.edu/~artem |
| E-mail: |  |
| Phone: | (956) 381-2577 |
| Office Hours: | Tue, Thu 10:35-11:35 am and 1:30-2:30 pm, or by appointment |
| |
| Teaching Assistant: | Eric Gonzalez |
| Office: | ENGR 2.212 |
| Web: | N/A |
| E-mail: | eric [at] plasticgnome [dot] com |
| Phone: | N/A |
| Office Hours: | Monday 9:00 am - 12:00 pm and Friday 3:00-5:00 pm, or by appointment |
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 4333 Database Design and Implementation.
- 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 the 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.
- Understanding of the database technology.
- Understand what are databases, DBMSs, transactions, TPSs, OLAP, and OLTP.
- Know the history and key players in the modern database world.
- Understand common database models, including relational, object, object-relational, hierarchical, network, etc.
- Understanding of the relational data model.
- Understand main concepts of relational model: relations/tables, tuples/rows, attributes/columns, database and relation schemas.
- Understand constraints: integrity, key, foreign-key, syntactic, semantic, and reactive.
- 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.
- Understand the E-R approach.
- Understand entities and entity types, relationships and relationship types.
- Understand entity type hierarchies, participation constraints, and part-of relationships.
- Model enterprise data with ERDs.
- Translate ERDs into relational database schemas.
- Familiarity with the database design and normalization theory.
- Understand the gap between ERD-generated schemas and "good" database.
- Understand the problems with set-valued attributes, data redundancy, and various data anomalies.
- Understand basics of the relational normalization theory: decompositions, functional dependencies, and normal forms.
- Proficiency with relational algebra and Structured Query Language (SQL).
- Understand relational algebra and construct queries using it.
- Understand SQL and construct queries using it.
- Understand the relationship and interactions of relational algebra and SQL.
- Familiarity with database implementation issues.
- Understand the general architecture of an RDBMS.
- Understand the elements of a system catalog.
- Understand database physical storage organization: heap and sorted files, main memory and disk interactions.
- Understand indexes: clustered/unclustered, primary/secondary, sparse/dense, ISAM, B+ trees, hash indexes, etc.
- Understanding of the basics of query processing and query optimization.
- Understand external sorting.
- Understand evaluation methods for projection, selection, and set operators.
- Understand basic join algorithms: nested-loops, sort-merge, and hash joins.
- Understand the basics of query execution planning, plan cost, and plan selection.
- Familiarity with database tuning techniques.
- Understand basics of SQL DDL schema tuning: indexes, denormalization, vertical and horizontal partitioning, materialized views.
- Understand basics of SQL DML: query rewriting, eliminating sorting, allowing duplicates, minimizing communication.
- Proficiency with the administration and development in Oracle and MySQL.
- Implement an enterprise database in Oracle.
- Implement an enterprise database in MySQL.
- Proficiency with database application development.
- Understand the basics of JDBC/ODBC.
- Understand two and three tier architectures for designing database applications.
- Develop a GUI-based application with querying capabilities for Oracle.
- Develop a GUI-based application with querying capabilities for MySQL.
- Empirical evaluation of database systems.
- Understand the notions of efficiency and scalability.
- Test database performance.
- Further improvement of technical writing and oral presentation skills.
- Write professional reports on database design and implementation.
- Present on database design and implementation.
Textbook: Michael Kifer, Arthur Bernstein, and Philip M. Lewis, Database Systems: An Application Oriented Approach, Compete Version, 2/E, Addison-Wesley, 2006, ISBN 0321268458
Online Resources:
More suggested resources will be added as the semester progresses.
Scoring:
| Assignments | 15% |
| Quizzes | 5% |
| Mid-Term Exam | 25% |
| Project | 25% |
| Final Exam | 30% |
| | ------- |
| Total Score (Max) | 100% |
|
Grading:
| Total Scoring | Grade |
|---|
| 90-100% | A |
| 80-89% | B |
| 70-79% | C |
| 60-69% | D |
| 0-59% | F |
|
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.
There will be a number of unannounced quizzes 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.
- A student does not have to be in a team to complete the project.
- A team may not have more than three students.
- All team members must contribute to the success of the project.
- Teams must be formed within the first two weeks of the semester.
- Assignments must be submitted 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.
If you have a disability which will make it difficult for you to carry out the work as outlined here, or you need special accommodation/assistance due to a disability, please contact the Office of Services for Students with Disabilities ASAP. Appropriate arrangements/accommodations can be arranged.
The instructor reserves the right to alter this syllabus as necessary.