PowerPoint Presentation
CS 331: Database System Design and
Management
Week 1: Introduction to DBMS
Dr. Shantanu Sharma
Fall 2022
Thanks to Mike Carey, Chen Li, Henry F. Korth, Chen Li, Sharad Mehrotra, Raghu Ramakrishnan, Avi Silberschatz, S. Sudarshan, Jeffrey Ullman, and many more for providing sample slides.
CS 331
• Credit: 3
• Instructor: Dr. Shantanu Sharma
• Location: Kupfrian Hall 209
• Class sessions: Tuesday and Thursday 11:30 AM – 12:50 PM
• Instructor Office: GITC 4201B
• Instructor email: XXXXXXXXXX
• Office Hours: Thursday (2:00 PM – 3:30 PM) or by
appointment over email.
• Office Hours Location: GITC 4201B
2
Prerequisite
•CS 114. Introduction to Computer Science II, o
•CS 116. Introduction to Computer Science II in C++, o
• IT 114. Advanced Programming for Information
Technology.
3
Who Am I?
• Assistant Professor in Computer Science at NJIT
• Postdoc – University of California, Irvine
• Advised by Prof. Sharad Mehrotra
• PhD – Ben-Gurion University, Israel
• Advised by Prof. Shlomi Dolev and Prof. Jeffrey D. Ullman
• Expertise
• Databases, IoT, Blockchain, and security/privacy aspects
4
Who Am I?
• Assistant Professor in Computer Science at NJIT
• Expertise
• Databases, IoT, Blockchain, and security/privacy aspects
• Looking for
• PhD students
• MS thesis students
• PhD/MS students who have not decided their PhD/MS Advisors
• Students working with faculties as future PhD/MS students and have not formally informed to the department → Please do not reach out to me to become an
advisor
•Undergrad students
• For independent study, research project, undergrad project
5
Course Overview
• This course will give a
oad overview of database management systems
• Fundamental concepts of databases will be explained
• Topics
• Database system architecture
• Data modeling using the entity-relationship model
• Storage of databases
• Query languages,
• Indexing
• Functional dependencies and normalization for relational database design,
elation decomposition
• Concu
ency control
• Transaction management
• Big data processing systems (Hadoop and Spark)
6
Outcome
• Gain knowledge of the requirements and applications of the
database management system
• Understand the fundamental components and operations
supported by a database management system
• Gain experience with a DBMS
7
Primary textbooks
• Lecture slides will cover major topics
• Most of the lectures will be based on the
two books
• Lecture slide availability:
• Slides will be online on Monday and
Wednesday before 11:59 PM.
• If there will be any change in the slide
efore/during/after the lecture, they will
e updated after the lecture.
8
Other reference books
• Fundamentals of Database Systems (7th edition) by Ramez
Elmasri and Shamkant Navathe
• Database Systems: The Complete Book by Héctor García-
Molina, Jeffrey Ullman, and Jennifer Widom
• Architecture of a Database System by Joseph M. Hellerstein,
Michael Stone
aker, and James Hamilton
• Principles of Database and Knowledge-Base Systems, Volume I,
y Jeffrey D. Ullman.
9
Readings
• Students are required to read the following papers
• Before mid-term:
• E. F. Codd: A Relational Model of Data for Large Shared Data Banks.
Commun. ACM 26(1): XXXXXXXXXX).
• Goetz Graefe: Query Evaluation Techniques for Large
Databases. ACM Comput. Surv. 25(2): XXXXXXXXXX).
10
Grading Policies
11
Quiz (Biweekly in the class) 10
Assignments 20
Project 15
Mid-term exam 20
End-term final exam 30
Attendance 5
Total 100
Bonus point (see details below) 10
I reserve the right to make small adjustments to grade weights or to add
emove
assignments, project, or quizzes as the need arises.
Grading Policies
12
Grade letter Points
A 85 and above
B+ 75 – 84
B 65 – 74
C+ 55 – 64
C 45 – 54
D 35 – 44
F 34 or below
I reserve the right to make small adjustments to grade weights or to add
emove
assignments, project, or quizzes as the need arises.
Quizzes
• Each quiz will contain 10 to 15 questions (multiple-choice, fill-in-the-blank, or small questions).
• Each quiz will be in the classroom.
• 15 mins will be given to return the sheet.
• Each student must submit their own quiz. No groups will be allowed.
• The quizzes will be on the following dates:
• 09/15
• 09/29
• 10/13
• 10/25
• 11/10
• 11/29
• 12/13
• Grading of quizzes: In the final grade, an average of the best of the six quizzes will be taken.
• For example, if one student receives the following points in the above seven quizzes: 10, 9, 8, 10, 6, 4, 10
espectively, then only the following points will be considered 10, 9, 8, 10, 6, 4, 10 (points of the 6th quiz will
not be counted). To include the quiz points in the final grade, we do the following: XXXXXXXXXX)/6 =
9.5.
• The grade of each quiz will be published before the next quiz.
13
Assignments
• Three assignments will be given.
• Each student must submit their own assignments. No groups
will be allowed.
• Each assignment will contain 10 questions of 10 points each.
• The assignment will be published on the following date and will
e due as mentioned in the following table
Assignment number Published on web Due date and time
1 09/30 10/10 11:59PM
2 10/31 11/10 11:59PM
3 11/28 12/09 11:59PM 14
Assignments
• All assignments are to be submitted to canvas on the date due.
• Assignments should NOT be handwritten. Students may use
Microsoft Word, Google Doc, or Overleaf.
• Grading of assignments: In the final grade, an average of the
est of the three quizzes will be taken.
• The grade of each assignment will be published before
publishing the next assignment.
• Late submission
• Allowed but with the given reasons.
• Each day will cause a deduction of 5 points. However, assignments
will not be accepted after the 20th of each month and result in zero
point. Without reason, late submissions are not allowed.
15
Project
• The project will be given to the students on 10/10.
• The project report and project code will be due on 12/02.
• The project report must be detailed and show why the student
has selected a particular approach over other approaches,
what were the challenges to solve the problem, the design
criteria, test-case scenarios, and other details.
17
Project
• Students can form a group of size 2-4.
• The group information must be emailed to the instructor and
TA before 10/18.
• Each student in the group will be evaluated separately. That is
the two members of the group may not get the same points.
• More details will come late
18
Mid-term and end-term exams
• All exams are open book, notes, and slides.
• Internet access is not allowed.
• Mid-term exam date: 10/27 Thursday.
• End-term date: will be announced.
• The duration of mid-term and end-term exams will be
announced.
19
Class Participation
• Interactive classes
• 4-5 Guest lectures
• Attending and participating in each lecture will result in 1 point.
• First guest lecture:
• Speaker: Avigdor Gal from Technion - Israel Institute of Technology
• Title: Loch Data and Other Monsters: on Creating Data Ecosystems,
the Intelligent Way
• Date and time: Wednesday, September 21, 2022, 2:30 PM – 3:30 PM
• Location: GITC 4402
20
Bonus Points
• Earn 10 bonus points.
• However, if a student obtains XXXXXXXXXXpoints in the final grade without a
onus, then their final grade will be A. Students who will earn the final
grade less than 80 can improve their grade in the final grade, if they earn 10
onus points.
• To earn the 10 bonus points, a student needs to read a research papers and
discuss/present it in the class for 20.
• A student needs to inform the instructor in advance.
• Students may select a research paper of their own choice. But they need
to inform the instructor and get acceptance before they start
eading/preparing. The research paper must be on the topics on
cybersecurity and from conferences such as SIGMOD, VLDB, ICDE, CIDR.
• Each student is allowed to present only a single research paper.
• Students can also use the slides of the paper available online.
21
Academic Integrity
• Any type of cheating and/or plagiarism is NOT allowed in this
course.
• We will follow NJIT University Policy on Academic Integrity.
Please see details here:
https:
www5.njit.edu/policies/sites/policies/files/academic-
integrity-code.pdf
• Note for Students with Disabilities
• We will follow the university rules in this regard.
22
Weekly Schedule of the Lectures (Tentative)
23
Week
numbe
Date Topic to be covered Note
1 09/06 &
09/08
Introduction The first week of the class
2 09/13 &
09/15
Relational Model and Alge
a 1st quiz on 09/15
3 09/20 &
09/22
Relational Model and Alge
a
4 09/27 &
09/29
SQL 2nd quiz on 09/29 + 1st assignment will be
online on 09/30
5 10/04 &
10/06
Project will be online (tentative)
6 10/11 &
10/13
3rd quiz on 10/13 + 1st assignment is due
on 10/10
Weekly Schedule of the Lectures (Tentative)
24
Week
numbe
Date Topic to be covered Note
7 10/18 &
10/20
Data storage
8 10/25 &
10/27
Review class + Mid-term 4th quiz on 10/25
Mid-term exam on 10/27
9 11/01 &
11/03
Indexes 2nd assignment will be online
on 10/31
10 11/08 &
11/10
5th quiz on 11/10 + 2nd
assignment is due on 11/10
11 11/15 &
11/17
Query execution and optimization
12 11/22
Weekly Schedule of the Lectures (Tentative)
25
Week
numbe
Date Topic to be covered Note
13 11/29 &
12/01
Database design and functional
dependencies
6th quiz on 11/29 + 3rd
assignment will be online on
11/28
14 12/06 &
12/08
Big-data, MapReduce, Hadoop,
Spark
3rd assignment is due on
12/09 + Project submission
15 12/13 Discussion and others (ACID vs
BASE)
7th quiz on 12/13 + Last week
of class
•Questions?
•For any question, do not
hesitate to email me.
• On weekends, there may be some delay in answering
26
Let us start our tech parts of the class
Make the class interactive
Bidirectional communication
Slides are based on
• Database management systems (3rd edition) by Raghu Ramakrishnan and Johannes Gehrke
• Database System Concepts (7th edition) by Avi Silberschatz, Henry F. Korth, and S. Sudarshan
• Fundamentals of Database Systems (7th edition) by Ramez Elmasri and Shamkant Navathe
• Database Systems: The Complete Book by Héctor García-Molina, Jeffrey Ullman, and Jennifer Widom
• Architecture of a Database System by Joseph M. Hellerstein, Michael Stone
aker, and James Hamilton
• Principles of Database and Knowledge-Base Systems, Volume I, by Jeffrey D. Ullman.
28
Content
• Database Management System (DBMS)
• Purpose of Database Systems
• Levels of Abstraction
• Data Models
29
Database Management System (DBMS)
• DBMS contains information about a particular enterprise
• Collection of inte
elated data
• Set of programs to access the data
• An environment that is both convenient and efficient to use
• Database Applications
• Banking: all transactions
• Airlines: reservations, schedules
• Universities: registration, grades
• Databases touch all aspects of our lives
30
Purpose of Database Systems
• Why not file systems?
• Data redundancy and inconsistency
• Multiple file formats, duplication of information in different files
• Difficulty in accessing data
• Need to write a new program to ca
y out each new task
• Data isolation — multiple files and formats
• Integrity problems
• Integrity constraints (e.g. account balance > 0) become “buried” in program
code rather than being stated explicitly
• Hard to add new constraints or change existing ones
31
Purpose of Database Systems
• Why not file systems?
• Atomicity of updates
• Failures may leave database in an inconsistent state with partial updates
ca
ied out
• Example: Transfer of funds from one account to another should either
complete or not happen at all
• Concu
ent access by multiple users
• Concu
ent accessed needed for performance
• Uncontrolled concu
ent accesses can lead to inconsistencies
• Example: Two people reading a balance and updating it at the same time
• Database systems offer solutions to all the above problems
32
Levels of Abstraction - One of the most important
enefits of using a DBMS
• Physical level
• Describes how a record (e.g., customer) is stored
• Relations stored as unordered files.
• Index on the first column of Students.
• Logical or conceptual level
• Describes data stored in the database, and the relationships
among the data
• Students(sid: string, name: string, login: string, age: integer, gpa:real)
• Courses(cid: string, cname:string, credits:integer)
• Enrolled(sid:string, cid:string, grade:string)
• View level
• Views describe how users see the data
• Application programs hide details of data types
• Views can also hide information (such as an employee’s salary) for
security purposes
• Course_info(cid:string,enrollment:integer)
33
Physical
Conceptual or Logical
View 1 View 2 View 3
Data Independence – Example 1
customer_id first_name total_item_purchased
34
Alice
Give me the count of the total item purchased by each
customer with their first name
You can give me results in a table form
View
Data Independence