CSCI 6315 Applied Database Systems

Spring 2016

 

 

Instructor: Xiang Lian

Office: ENGR 3.275

Web: http://faculty.utpa.edu/lianx/

Telephone Number: (956) 665-2472

Email: xiang.lian@utrgv.edu

Course: CSCI 6315 Applied Database Systems  

Prerequisites: CSCI 6302, or consent of instructor

Time: W, 5:55pm ~ 8:25pm

Location: ENGR 1.290

Course Webpage: http://faculty.utpa.edu/lianx/CSCI6315_2016spring.html

 

Instructor's Office Hours: Tuesday and Thursday (2:00pm ~ 5:00pm); or by appointment

 

Teaching Assistant: Long Lu

Office: ENGR 3.273

E-mail: long.lu01@utrgv.edu

Phone: TBA

TA's Office Hours: Tuesday (4:00pm ~ 5:30pm); or by appointment

 

 

Textbook

Thomas Connolly and Carolyn Begg. Database Systems: A Practical Approach to Design, Implementation, and Management, 6th Edition. Addison-Wesley, 2015, ISBN-10: 0132943301, ISBN-13: 9780132943307.

Online Resources:

 

Description

This course covers the application of a modern database system. Concepts covered include relational model, normalization, structured query language, Internet data formats, and server and client side technologies. The course is targeted at students who are interested in the development of application programs using a database system such as Oracle, Teradata, or Microsoft SQL. Prerequisite: CSCI 6302 or equivalent.

The course 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

·         Triggers and active databases

·         Using SQL in an application

·         The basics of query processing

·         Database tuning

·         MySQL

·         Data warehousing

·         Transaction management

·         Database systems and the World Wide Web

·         Distributed database systems

 

Objectives

·         Become familiar with database technology

·         Understand the relational data model

·         Learn and apply conceptual data modeling techniques

·         Become familiar with database design

·         Master relational algebra and Structured Query Language

·         Become familiar with database implementation issues

·         Learn database indexing and tuning techniques

·         Learn how to administer and develop a MySQL database

·         Gain practical experience in database and database application development

·         Learn about techniques such as transaction management and data warehousing

·         Improve technical writing and oral presentation skills

 

Tentative Schedule

Week

Topic

Notes1

Week 1 (Jan. 20)

Introduction (Ch. 1, 2);

Reading Materials: Database Design (Ch. 10, 11)

Please form a team of 2-3 members, and send (Student ID, Student Name) of team members to xiang.lian@utrgv.edu (Deadline: Jan. 27).

Week 2 (Jan. 27)

Entity-Relationship Modeling (Ch. 12), examples (solutions)

Assignment 1 (Due on Feb. 10)

Introduction to Project (Template for Project Report)

Project Part 1  (Due on Feb. 24; Please submit Sections 1-4 of the project report)

Week 3 (Feb. 3)

Enhanced Entity-Relationship Modeling (Ch. 13); Relational Model (Ch. 4)

Exercises (1)

Week 4 (Feb. 10)

Relational Algebra (Ch. 5.1)

 

Q/A

 

Project Report (1) [Sections 1, 2, and 3] (soft deadline: Feb. 10; the score of this submission will not be counted, but you will receive my feedback)

Assignment 2 (Due on Mar. 2)

Exercises (2) (Valentine’s bonus exercises; 2 extra points; Hard Deadline: due on Feb. 17)

Review for Midterm Exam I

 

Week 5 (Feb. 17)

EXAM I

 

Week 6 (Feb. 24)

SQL (Ch. 6, 7), examples

Assignment 3 (Due on Mar. 9)

Project Part 2 (Due on Mar. 23)

Week 7 (Mar. 2)

SQL (Ch. 7, 8), trigger examples

(Group Meeting for Project)

Exercise (3)

Week 8 (Mar. 9)

Normalization (Ch. 14, 15), reading materials

Exercise (4)

Exercise (5)

Week 9 (Mar. 16)

--

March 14 - 18, Spring break; No classes

Week 10 (Mar. 23)

Transaction Management (Ch. 22), reading materials

Project Part 3 (Due on Apr. 20; Hard deadline for demonstration: April 29)

Exercise (6)

Week 11 (Mar. 30)

Query Processing (Ch. 23)

(Group Meeting for Project)

Assignment 4 (Due on Apr. 27)

Week 12 (Apr. 6)

File Organization and Indexes (Appendix F – online), reading materials

Q/A

Review for Midterm Exam II

Week 13 (Apr. 13)

EXAM II

April 13, Drop/Withdrawal deadline

Week 14 (Apr. 20)

Distributed Database Systems (Ch. 24, 25)

(Group Meeting for Project)

Project Part 4 (Bonus Project; Hard Deadline: May 4)

Week 15 (Apr. 27)

Data Warehousing (Ch. 32, 33), reading materials

(Group Meeting for Project Presentations)

 

Week 16 (May 4)

Project Presentations

Group #1: Little Soccer Division (LSD)

Group #2: Gallery 404

Group #3:

Group #4: Hospital Management System (HMS)

Group #5: Jasmine Project

Group #6:

Extra 5 project points, rated by other team members. (Hard Deadline: May 6)

Review for Final Exam

Week 17 (May 6 - 12)

Final Exam (5:55pm-8:25pm, Wednesday, May 11)

 

 

1 Academic calendar: http://www.utrgv.edu/_files/documents/admissions/utrgv-academic-calendar-2016-2017.pdf

Final exam schedule: http://www.utrgv.edu/_files/documents/admissions/spring-2016-final-exam-schedule-1-8-16.pdf

Note: Exam dates are tentative, and 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.

 

MySQL: The world's most popular open source relational database

Oracle: The #1 commercial relational database (48.8% market share in 2011)

 

Scoring and Grading

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

 


Lecture Attendance Policy

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.

Students are expected to attend all scheduled classes and may be dropped from the course for excessive absences.  UTRGV’s attendance policy excuses students from attending class if they are participating in officially sponsored university activities, such as athletics; for observance of religious holy days; or for military service. Students should contact the instructor in advance of the excused absence and arrange to make up missed work or examinations.


Make-up Exam Policy

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.


Late Work Policy

·         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.


Academic Dishonesty Policy

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

If you have a documented disability (physical, psychological, learning, or other disability which affects your academic performance) and would like to receive academic accommodations, please inform your instructor and contact Student Accessibility Services to schedule an appointment to initiate services. It is recommended that you schedule an appointment with Student Accessibility Services before classes start. However, accommodations can be provided at any time. Brownsville Campus: Student Accessibility Services is located in Cortez Hall Room 129 and can be contacted by phone at (956) 882-7374 (Voice) or via email at accessibility@utrgv.edu. Edinburg Campus: Student Accessibility Services is located in 108 University Center and can be contacted by phone at (956) 665-7005 (Voice), (956) 665-3840 (Fax), or via email at accessibility@utrgv.edu.


Mandatory Course Evaluation Period

Students are required to complete an ONLINE evaluation of this course, accessed through your UTRGV account (http://my.utrgv.edu); you will be contacted through email with further instructions. Online evaluations will be available Nov. 18 – Dec. 9, 2015. Students who complete their evaluations will have priority access to their grades.


Sexual Harassment, Discrimination, and Violence 

In accordance with UT System regulations, your instructor is a “responsible employee” for reporting purposes under Title IX regulations and so must report any instance, occurring during a student’s time in college, of sexual assault, stalking, dating violence, domestic violence, or sexual harassment about which she/he becomes aware during this course through writing, discussion, or personal disclosure. More information can be found at www.utrgv.edu/equity, including confidential resources available on campus. The faculty and staff of UTRGV actively strive to provide a learning, working, and living environment that promotes personal integrity, civility, and mutual respect in an environment free from sexual misconduct and discrimination.


Learning Outcomes

·        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.


Program Outcomes

 

·         (a) An ability to apply knowledge of mathematics, science, and engineering.

·         (c) An ability to design a system, component, or process to meet desired needs within realistic constraints such as economic, environmental, social, political, ethical, health and safety, manufacturability, and sustainability.

·         (d) An ability to function on multidisciplinary teams.

·         (e) An ability to identify, formulate, and solve engineering problems.

·         (f) An understanding of professional and ethical responsibility.

·         (g) An ability to communicate effectively.

·         (j) A knowledge of contemporary issues.

·         (k) An ability to use the techniques, skills, and modern engineering tools necessary for engineering practice.


Disclaimer

The instructor reserves the right to alter this syllabus as necessary.