CS 4163/6523: Introduction to Database Systems

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

CS 4163/6523: Introduction to Database Systems

Prerequisites: CS 2123 (Data Structures) and CS 3013 (Discrete Mathematics). Students should also have good programming skill in some object-oriented language.

Other references: Students may also find the following references useful for additional examples:

● Thomas Connolly & Carolyn Begg, Database Systems: A Practical Approach to Design, Implementation, and Management, (6th Edition), 2015.

● Michael Kifer, Arthur Bernstein, & Philip M. Lewis, Database Systems: An Application-Oriented Approach (Second Edition), Addison-Wesley, 2006.

Class meetings and computer requirement for exams

Classes will meet on Harvey using Collaborate. We will also utilize Harvey for sharing pre-recorded lecture videos, lecture slides, and for homework, project, and quiz submissions. You must be able to remotely connect to Harvey for accessing and completing assignments, quizzes and tests as well as participate in any synchronous online sessions using Collaborate. Latest version of Lockdown Browser and Respondus Monitor must be downloaded and used for taking in-class and final exams. Note that this requires that your computer must have a microphone and webcam installed and functioning properly.

        Students are required to review the pre-recorded lecture videos and lecture slides before joining synchronous class sessions. Class time will be used primarily to answer questions about the lectures, for additional problem solving, and for administering in-class quizzes at the instructor’s discretion. Class attendance is required.

        If needed, we might use a part-time TA for the course who would be available to meet for a couple of hours a week. The preferred mode for meeting the TA should be online, but in-person meetings can be arranged (see section on “COVID-19 related policies” for more details).

Course Objectives

This course will provide a thorough introduction to the theory and practice of database systems. The emphasis will be on theoretical considerations involved in modeling and representing data and in designing easy to use, reliable and efficient database management systems. The course also covers practical aspects of query languages and optimization, transaction processing, concurrency control and recovery techniques as well as embedding structured queries in high-level language. Students will also use widely used database management system for multiple projects.

CS Objectives: This course will improve the following capabilities for the students:

● An ability to analyze a problem, and identify and define the computing requirements appro-priate to its solution.

● An ability to use current techniques, skills, and tools necessary for computing practice.

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

Student Learning Outcomes

Upon successfully completing this course, a student is expected to possess the following capabilities:

● Develop Enhanced Entity-Relationship (EER) Models from user specifications

● Translate EER models to Relational Schemas and associated constraints

● Query relational models with Relational Algebra and Relational Calculus

● Implement relational schemas into relational databases, query populated databases with SQL, use views and triggers

● Use heuristics to optimize SQL queries

● Perform Database Programming with Function Calls and Class Libraries

● Evaluate the quality of database design using the concepts of functional dependencies and normal forms

● Synthesize effective database designs given functional dependencies constraints

● Understand ACID proerties of transactions and characterize transaction schedules based on serializability and recoverability

● Apply locking and timestamp based concurrency control techniques

● Apply deferred and immediate update protocol based recovery from failure techniques

● Understand the characteristics of NOSQL systems and the CAP theorem

● Understand basic data mining concepts including association rule mining

● Understand basic database security issues including discretionary and mandatory access con-trol mechanisms.

COVID-19 related policies

We will share all course material through Harvey and class discussions, instructor office hours, exams, etc. will be held on Harvey or Microsoft Teams. In-person meetings are not anticipated. The following statement is being included as a required staement from the university: Students should download the Daily Wellness Check by Microsoft Power Apps and use it daily to report any symptoms that may be related to COVID-19 or a possible exposure to COVID-19. You must complete the Daily Wellness Check and receive clearance to come to campus prior to attending this class. If you are not cleared to come to class, stay in your residence, call Alexander Health CEnter for next steps, and e-mail to let me know you will not be able to attend class. If, for any unforeseen reason, you want to meet in person with the part-time TA for the course, you must complete the Daily Wellness Check and receive clearance to come to campus on the day of the meeting and must wear mask during the meeting.

Course Content & Schedule

We will be covering parts of Chapters 1–10, 14–15, 19–22, 24–25 and 28 from the textbook.

        The following schedule lists the topics to be covered in each class:

Week 1

Lecture 1: Course Introduction, Databases (DB) and Database Management Systems (DBMS), DB Architectures, Data Modeling

Lecture 2: Entity-Relationship (ER) Models

Week 2

Lecture 3: Enhanced ER (EER) Models

Lecture 4: Relational Data Model & Relational Database Constraints

Week 3

Lecture 5: Mapping from ER and EER to Relational Data Models

Lecture 6: Formal Query Language for Relational Data Models: Relational Algebra

Week 4

Lecture 7: Formal Query Language for Relational Data Models: Relational Calculus; QB2

Lecture 8: Relational Database Specification with SQL DDL; Simple SQL Queries

Week 5

Lecture 9: Advanced SQL Queries Lecture 10: More SQL Queries; Views, Triggers, Assertions; Schema Modification With SQL

Week 6

Exam 1 review; Exam 1

Week 7

Lecture 11: Heuristic Query Optimization; Database Programming Techniques: JDBC

Lecture 12: Functional Dependencies (FDs); Guidelines for Relational Database Schema Design

Week 8

Lecture 13: Normal Forms: 1NF, 2NF, 3NF

Lecture 14: Normal Forms: BCNF; Inference Rules for FDs and Minimal Cover

Week 9

Lecture 15: Dependency Preservation (DP) & Lossless Join (LJ) Checks; Relational DB Synthesis: DP & LJ 3NF, LJ BCNF

Lecture 16: Transaction Processing Concepts

Week 10

Exam 2 review; Exam 2

Week 11

Lecture 17: Characterizing Transaction Schedules Based On Recoverability & Serializability

Lecture 18: Concurrency Control: Two-Phase Locking

Week 12

Lecture 19: Avoiding/Resolving Deadlocks and Starvation, Concurrency Control with Timestamps

Lecture 20: DB Recovery Techniques

Week 13

Lecture 21: NOSQL Databases, CAP Theorem; Data Mining Concepts: Association Rule Mining

Lecture 22: DB Security

Week 14

Exam 3

Finals review

Final Exam

Evaluation

The approximate breakdown of grades for the course is as follows:

Homeworks           20%

Projects                15%

Quizzes                 15%

In-class Exams       30%

Final                      20%

                              —-

Total                     100%

        Graduate students will be assigned an extra assignment worth 5% of the course grade.

Important Dates:

Exam 1 (tentative) . . . . . . . . . . . . . . . . Thursday, Sep 30

Exam 2 (tentative) . . . . . . . . . . . . . . . . .Thursday, Oct 28

Exam 3 (tentative) . . . . . . . . . . . . . . . . . .Tuesday, Nov 30

Finals Exam . . . . . . . . December 16, Thursday, 9–11:25am

Class policies

Refer to the Class meetings and computer requirement for exams for basic class policies. Students are urged to contact the instructor in case of any difficulty with the course material, homeworks, etc. Students are also encouraged to study together and discuss concepts covered in the course. However, all assigned homeworks and projects must be performed individually; no collaboration is allowed. It is strongly recommended that you contact the instructor, and not other students, in case you need any clarifications on assignments. Late submissions for homeworks and projects will be accepted for up to 3 days with a 25% penalty.

        Programming assignments will require use of a DBMS hosted on the ENS server.

Make-up examinations will not be given unless a valid and excused absence (e.g., a university sponsored event or trip or a serious illness) is documented and approved by the Student Success office. The student should contact the instructor before missing an examination. If a make-up examination is approved, it should be taken as soon after the exam date as possible. 

Academic Misconduct: Students are expected to act in accordance with all department, college and university policies. The academic misconduct policy of TU for undergraduate and graduate students will apply. All examinations are closed book, unless otherwise noted, and must reflect individual effort. Students can discuss the general concepts underlying a programming assignment, but at no point of time should you look at or help with another student’s code! Submitted code must be your own work. We plan to run plagiarism detection software on submitted assignments. Programs that are flagged by the software or raise “red flags” will result in a range of penalties, depending on the nature of violation, will be penalized and further actions may be taken. The penalties and actions range from at the minimum assigning zero credit, to dropping a grade in the course, to being assigned an ‘F’ grade for the course, to the involved student being reported to university authorities which might result in dismissal from the university. Academic dishonesty is a serious offense which undermines quality of education and the reputation of our university and will not be tolerated.

Rules and conduct for using computers: The following rules govern the proper use of comput-ers and related resources at The University of Tulsa. Violation of such rules constitutes academic misconduct. Penalties may range from reduction of course grade to failing the course to dismissal from the university.

1. The computer account allocated to you is to be used only by you and only for assigned course work at TU.

2. Logging in, running programs under, accessing or using information in computers or computer accounts other than those assigned to you constitutes academic misconduct and is strictly prohibited.

3. Giving your account number and password to anyone else constitutes academic misconduct and is strictly forbidden.

4. Reading, copying, modifying or deleting files created by other people is not allowed.

5. Lecture slides, videos, programs and data supplied by an instructor for use in courses remain the property of the instructor and/or The University of Tulsa. These items may not be copied or reused without the express consent of the instructor.

Student Success Team statement: All students are encouraged to familiarize themselves with and take advantage of services provided by The Student Success Team, including Student Access, Student Success Coaching, and tutoring. To request a student success coach to improve study skills, email [email protected]. To request a tutor, email [email protected].

Student Access statement: Students who have or believe they may have a disability and would like to set up accommodations should contact Student Access within the Student Success Team to discuss their needs and facilitate their rights under the Americans with Disabilities Act and related laws. Student Access provides private consultations to any student. Contact Student Access staff by email at [email protected] or by phone at 918-631-2315. The online application for accommodations may be obtained here. Student Access staff will assist students in the implemen-tation of approved accommodations. Students who qualify for accommodations should meet with the instructor privately (during office hours or by appointment) as soon as possible to arrange for their needs and obtain support for the class.