The database in one of several commercial relational database management systems such as Oracle or Microsoft SQLServer. The material presented in this chapter is tutorial in nature and covers the COMPANY database design of the Elmasri/Navathe text in detail. Sep 07, 2014  Classification of DBMSs Distributed Database Systems have now come to be known as client server based database systems because they do not support a totally distributed environment, but rather a set of database servers supporting a set of clients. Elmasri and Navathe, Fundamentals of Database Systems.

For each video listed here, you can download the unnanotated slides (), annotated slides (), and any files used in SQL or XML demonstrations (). Not all videos have slide annotations.
Apologies to those of you who cannot read pptx files. We will put up pdf versions of the slides soon.
If you're following along with a textbook (and we encourage it!), we include a mapping of videos to sections in four of the most commonly used database textbooks:
A First Course in Database Systems (3rd edition) by Ullman and Widom (U/W)
(same material and sections as Database Systems: The Complete Book (2nd edition)
by Garcia-Molina, Ullman, and Widom)
Database Management Systems (3rd edition) by Ramakrishnan and Gehrke (R/G)
Fundamentals of Database Systems (6th edition) by Elmasri and Navathe (E/N)
Database System Concepts (6th edition) by Silberschatz, Korth, and Sudarshan (S/K/S)

I. Introduction			U/W	R/G	E/N	S/K/S
Introduction to the course	1.1 - 1.2	1.1 - 1.4	1.1 - 1.9	1.1 - 1.14

II. Relational Databases			U/W	R/G	E/N	S/K/S
The relational model	2.2 - 2.3	1.5, 3.1	3.1	2.1 - 2.2
Querying relational databases	---	1.6	---	---
Other readings: SQL for Web Nerds (dated, but still a good read)

III. Relational Algebra			U/W	R/G	E/N	S/K/S
Select, project, join	2.4	4	6.1 - 6.3	6.1
Set operators, renaming, notation	'	'	'	'

All SQL scripts use the same schema and data, and work in SQLite, MySQL, and Postgres except as noted. See our quick guide to SQL for installing and using these systems. All SQL demos use the Database Master interface.

IV. SQL		U/W	R/G	E/N	S/K/S
Introduction to SQL
Basic SELECT statement	6.1	5.2	4.3	3.3
Table variables and set operators	6.2	5.3	'	3.4 - 3.5
Subqueries in WHERE clause		6.3	5.4	5.1.2 - 5.1.6	3.8
Subqueries in FROM and SELECT	'	'	'	'
Aggregation		6.4.3 - 6.4.7	5.5	5.1.7 - 5.1.8	3.7
NULL values		6.1.6-7, 6.4.6	5.6	5.1.1	3.6
Data modification statements	6.5	3.1.1	4.4	3.9
Other readings: In addition to the textbook material, there are many entire books specifically about getting started with SQL; try searching for SQL in Amazon for a representative selection.

See our quick guide to XML validation and querying for the tools used in these demos.

V. XML Data					U/W	R/G	E/N	S/K/S
Well-formed XML		11.2	7.4.1	12.2	23.1 - 23.2
DTDs, IDs & IDREFs	11.3	7.4.2	12.3.1	23.3.1
XML Schema	11.4	---	12.3.2	23.3.2
Other readings: There are online introductions to XML, DTDs, and XML Schema at W3Schools, and XML and DTD tutorials at Zvon.org. The W3C has the complete specifications for XML/DTDs and XSDs.

See our quick guide to XML validation and querying for the tools used in these demos.

VI. Querying XML			U/W	R/G	E/N	S/K/S
XPath	12.1	27.7.1	12.5.1	23.4.1 - 23.4.2
XQuery	12.2	27.7.2 - 27.7.4	12.5.2	23.4.3
XSLT	12.3	---	---	---
Other readings: There are online introductions to XPath, XQuery, and XSLT at W3Schools, and XPath and XSLT tutorials at Zvon.org. The W3C has the complete specifications for XPath, XQuery, and XSLT.

VII. Relational Design Theory			U/W	R/G	E/N	S/K/S
Relational design overview			15.1	8.1
Functional dependencies	3.1 - 3.2	19.1 - 19.3	15.2	8.3
Boyce-Codd normal form	3.3	19.4.1, 19.6.1	15.5	8.3.2, 8.5.1
Multivalued dependencies, 4th normal form	3.5 - 3.6	19.8.1 - 19.8.2	15.6	8.6
Shortcomings of BCNF/4NF	3.4	---	---	8.8.4

VIII. Unified Modeling Language (UML)			U/W	R/G	E/N	S/K/S
UML data modeling	4.7	2.7	10.3	7.9.2
UML to relations	4.8	---	---	---
Other readings: An additional resource for the video is found in the initial portions of the following sections: models, classes, associations, many-to-many associations (both pages 1 and 2), subclasses, and aggregation and composition. The material relating UML designs to relations is also relevant, but is approached at a more detailed level than we cover in our 'UML to relations' video.

See the first SQL section above for information and links applicable to all of our SQL demos.

IX. Constraints and Triggers				U/W	R/G	E/N	S/K/S
Motivation & overview
Constraints of several types	7.1 - 7.2	3.2 - 3.3, 5.7	3.2 - 3.3	4.4
Referential integrity	7.1.1 - 7.1.2	3.2.2	3.2.4	4.4.5
Triggers introduction		7.5	5.8 - 5.9	26.1.1	5.3
Triggers demo (part 1)
Triggers demo (part 2)

X. Indexes			U/W	R/G	E/N	S/K/S
Indexes	8.3 - 8.4	8.3-5, 20.2-6	18, 20.2	11

See the first SQL section above for information and links applicable to all of our SQL demos.

XI. Views				U/W	R/G	E/N	S/K/S
Defining and using views	8.1	3.6	5.3.1 - 5.3.2	4.2.1 - 4.2.2
View modifications - introduction		8.2	3.6.2	5.3.3	4.2.4
View modifications using triggers		8.2.3	---	---	---
Automatic view modifications	8.2.2	---	---	---
Materialized views	8.5	25.9	5.3.3	4.2.3

XII. Transactions			U/W	R/G	E/N	S/K/S
Introduction to transactions			21.1
Transaction properties	6.6.1 - 6.6.3	16.1 - 16.3	21.2 - 21.3	14.1 - 14.7
Isolation levels	6.6.4 - 6.6.6	16.6	21.6	14.8

XIII. Authorization			U/W	R/G	E/N	S/K/S
Authorization	10.1	21.1 - 21.3	24.2	4.6

See the first SQL section above for information and links applicable to all of our SQL demos.

XIV. Recursion in SQL				U/W	R/G	E/N	S/K/S
Basic recursive WITH statement introduction		10.2	24.1	6.4.3?	5.4.2
Basic recursive WITH statement demo
Nonlinear and mutual recursion	10.2.2	'	---	---

See the first SQL section above for information and links applicable to all of our SQL demos.

XV. On-Line Analytical Processing (OLAP)		U/W	R/G	E/N	S/K/S
Introduction to OLAP		10.6 - 10.7	25.1 - 25.3	29.1 - 29.3	5.6
OLAP demo

Course Title

EECS/IT 746: Database Systems (Fall 2019)
Mondays, 6:10-9:00 PM, BEST 175 (Course Number: 29125)
Course Web Site: people.eecs.ku.edu/~saiedian/746

Prerequisites and Expected Audience. Eligibility for upper division EECS courses (or industrial experience in software development and programming). This course is intended for database engineers, software engineers, database managers, and graduate students in IT or computer science. Graduate standing in EECS, introductory courses in software engineering or systems analysis, or industrial experience in software development is assumed. Thought this may not be the absolute requirement, a student is expected to have a relatively good background in computing and discrete mathematics (and a working knowledge of Linux especially if the student wishes to use a Linux-based database system).

Instructor

Professor Hossein Saiedian
Offices: BEST 250 and Nichols 155
Telephone: 785-864-8812 or 913-897-8515
E-Mail: saiedian at ku.edu
WWW: people.eecs.ku.edu/~saiedian
Office Hours: Mondays, 1:00-5:00 PM (and by appointment)

Course Overview

The objective of this course is to provide a relatively comprehensive introduction to the modeling and design of databases and the uses of a database management systems (DBMS). Conceptual modeling via entity-relationship (ER) diagrams and UML, the relational data model, database design (functional dependencies, normal forms), query languages such as relational algebra and SQL, database design concepts such as integrity constrains, triggers, query optimization, transactions processing and concurrency control, and more recent topics such as database security, distributed databases, big data, and non-SQL databases data will be covered.

Textbooks

Wilfried Lemahieu, Seppe vanden Broucke, and Bart Baesens Principles of of Database Management, Cambridge, 2018.

Please visit the textbooks' websites for updates and errata.

The primary textbook is used for both undergraduate and graduate courses on (introduction to) database systems. As such many of its chapters will be briefly covered but students are required to thoroughly read them, especially if they do not have formal education in computer science or IT. Our objective is to spend more time on advanced topics such as database design and more emerging database topics.

Supplementary information for the course (e.g., PowerPoint slides, class announcements, the course syllabus, test dates, and other information) will be made available online. Students are responsible for lecture notes, reading assignments, as well as items distributed during the classroom sessions. Students are also responsible for regularly visiting the class website for topics covered and any date changes.

The order of chapter coverage may be different from the textbook. Unless explicitly stated, students are responsible for all sections in a chapter. In addition to the materials from the main textbook, students are responsible for lecture notes, reading assignments, as well as items distributed during the classroom sessions. Important reading materials as well as lecture slides will be placed on the class website.

Other Resources

Useful readings and tutorials (including slides from other sources will be made available (see the links below) and may be a part of the weekly lecture presentations. In addition to the above, students should also develop a habit of regularly browsing such journals as IEEE Computer, IEEE Software, and Communications of the ACM.

Evaluation Criteria (Subject to revision)

Students will be evaluated as follows:

• Grade Distribution

  * Quizzes and exams: 70%
  

  * Special project: 10% (optional)
  

  * Team project and individual assignments: 30% (The term project will have at least five components each worth 50 points that will be due in approximately three week intervals.)

  Whether you’re considering just a single-app subscrip­tion like Photoshop CC 2015.5 or getting the complete Creative Cloud, you can always use these free trials to install, run, and activate the new software on your system, without needing to reinstall. Photoshop cc 2015.5 activation offline.

• Grading Scale

  A =	90%.100%
  B =	80%.89%
  C =	70%.79%
  D =	60%.69%

• No make-up quizzes are given.No late work will be accepted. (Certain exceptions may be made for family emergencies, religious observance, and illnesses.)
• All written work must be typed. It is OK to draw diagrams by hand and then scan them, but they must be legible.

Special Project Options

A 'special project' will be optional (as much as 10%) this semester. The 'special project' provides an opportunity for each student to become an expert in an area related to the topic of the course. It can include a term paper or a thorough, workshop-like, 90--120 minute presentation that covers a related topic in-depth. A special project topic will have to be approved.

Those interested in a presentation should have experience in long, lively, and engaging presentations and should begin their preparation immediately. Carefully follow the Guidelines for Making a Presentation. A proposal (workshop topic, justification, list of resources, and the tentative date for the presentation) should be submitted by the third week of the semester.

Those who would like to do a term paper may choose an applied research topic, e.g., an evaluation or comparison of certain methodologies for a real case study (or a reconstruction of a case study reported in literature). Another option is to make an objective evaluation of several research projects tackling the same problem. Other ideas are welcome. Guidelines for Writing a Term Paper have to be carefully followed. The paper decision and the tentative topic should be made by the third week of the semester.

Weekly Schedule

The following is the weekly semester schedule of lecture topics and all related curricular activities. Some referenced documents may be password-protected. The password will be publicized in class.

Week 1: Monday August 26

Discussion topics: Course syllabus and course review, Term project selection and review, Term project team formation, Introduction to database management system

Readings: Chapter 1: DBMS concepts

Assignments: None

Week 2: Monday September 2

No class (Labour Day)

Week 3: Monday September 9

Discussion topics: DBMS architecture, conceptual modeling

Readings: Chapter 2 (DBMS architecture) and Chapter 3 (conceptual modeling)

Assignments: None

Week 4: Monday September 16

Discussion topics: Conceptual modeling with ER and UML, organizational aspects of data management; introduction to the relational model

Other topics: Due: Team Projects: A vision statement and/or a scope definition for the term project. Objective of the vision/scope statement: Motivation and scope definition, the choice of a DBMS (e.g., mySQL?), system users (users and applications), interface choice (e.g., web-based?), storage and processing requirements. Also include team title, team members, a description of tentative roles for each member, team member skills, contact information, etc.

Readings: Chapter 3 (conceptual modeling) and Chapter 4 (organizational aspects of data managemnet

Assignments: TBD

Week 5: Monday September 23

Discussion topics: Database security (guest speaker)

Other topics: Paper/workshop (special project) proposal is due

Readings: Elmasri and Navathe Chapter 30 (database security) and Chapter 6 (the relational data model, excluding normalization)

Assignments: TBD

Week 6: Monday September 30

Discussion topics: The relational model (excluding normalization) and relational algebra

Other topics: Review for exam 1

Readings: Chapter 6: the relational data model, and supplementary items on relational algebra

Assignments: TBD

Week 7: Monday October 7

Discussion topics: Introduction to SQL

Other topics: Exam 1

Readings: Chapter 7: SQL

Assignments: TBD

Week 8: Monday October 14 No class (Fall break) Week 9: Monday October 21

Discussion topics: Relational database design (database normalization)

Readings: Chapter 6 (normalization section)

Assignments: TBD

Week 10: Monday October 28

Discussion topics: Database design and normalization

Other topics: Due: Team Projects: A formal statement of the requirements and the conceptual model. The objective of the statement is to more formally describe the functional and non-functional requirements of the database system. Include appropriate comments about conceptual modeling. Include an ER (or UML) diagram for the problem. Be sure to underline the key attributes, show multiplicity, full/partial participation, specialization and generalization, etc., when applicable.

Due: Detailed outline for the term paper or workshop

Readings: Mostly lecture notes (some notes on database normalization)

Assignments: TBD

Week 11: Monday November 4

Discussion topics: Object-oriented and noSQL databases

Readings: Chapter 8 (OO databases) and Chapter 11 (NoSQL databases)

Assignments: TBD

Week 12: Monday November 11

Discussion topics: Database security, transaction management

Other topics: Review for exam 2

Readings: Chapter 14: Transaction management

Assignments: TBD

Week 13: Monday November 18

Discussion topics: Database APIs and data distribution

Other topics: Due: Team Projects: The logical relational model and schema (DDL in SQL), The objective: Map (transform) the conceptual schema in ER (or UML) into the data model of the chosen DBMS (in our case, the relational model), identify the functional dependencies, normalize the resulting relations, and to define external views. Be sure to clearly show the relations (tables), their attributes, primary and foreign keys. For each relation, indicate its functional dependencies and its normal form.

Exam 2

Readings: Chapter 15 (Accessing databases) and Chapter 16 (Data distribution)

Assignments: TBD

Week 14: Monday November 25

Discussion topics: Big data

Other topics: Due: First draft of the term paper

Readings: Chapter 19: Big data,

Assignments: TBD

Week 15: Monday December 2

Discussion topics: Query optimization, cloud DB, and database APIs

Other topics: Due: Team Projects: Physical database design and implementation. The objective is to define and implement the actual relations (tables), populate the relations with meaningful data, implement any necessary transactions or embedded program or online scripts. Provide SQL DDL definitions for your database. Provide a listing of each (mySQL) relation and its data. If your database will have Web interfaces, provide snapshots of such interfaces.

Readings: Chapter 20: Analytics

Assignments: TBD

Week 16: Monday December 7

Discussion topics: Semester review, project presentations

Other topics: Team Projects: Presentations and demos; Project portfolios including the user's manuals. Presenttion objective: 25-minute presentation by each team to present the design (architecture and the rationale) for their database project and to demonstrate (outside classroom) the best features of the project. The presentations normally should focus on the ER diagram (to provide a conceptual view), discuss the resulting relations (keys, attributes, etc.) and their normal form, a rationale for the normal form achieved, and a sampling of interesting SQL queries and their output.

Readings:

Assignments: TBD

Week 17: Monday December 14

Discussion topics: Comprehensive final exam

Important Dates

• First class day: Monday August 24
• Paper/workshop (special project) proposal: Monday September 16
• Exam 1: Monday October 7
• Fall break: October 12-October 15 (no class on October 14)
• Detailed outline for the term paper or workshop: October 21
• Exam 2: Monday November 11
• First draft of the term paper: Monday November 18
• Thanksgiving Holiday: November 20-November 24
• Team Projects due: Monday Monday December 2
• Team Project presentations: Monday December 9
• Last class day: Monday December 9
• Final draft of the term paper: Monday December 9
• Comprehensive final exam: Monday December 16, 6:00-8:00 PM

Attendance, ClassroomPresentations by Students, Guest Speakers

Attendance is important and required. If a student misses a classsession, he or she will be entirely responsible for learningthe materials missed without the benefit of a private lecture on theinstructor's part. Furthermore, the student will be responsible forfinding out what assignments may have been given and when they aredue.

We will have both instructor and students' presentations.Students are expected to read assigned articles from thetextbook or the reading list. Students are expected to actively participate in classroom discussions, make presentations,and regularly make contributions such as offering comments,asking interesting questions, and responding with good answers.

SuggestedReadings

The textbook is an excellent survey and tutorialresource. Most up-to-date topics on information andcomputer security can be found in technical journals andrecent conference proceedings. Students should developa habit of regularly browsing such journals asIEEE Software,IEEE Computer,Communications of the ACM, and similar journals.

E-mail communication is fast, flexible, and effective. You areexpected to have an @ku.edu email account and regularly checkit. Important classroom notes will be communicated via email.

You are a graduate student registered in a graduate course offered bythe School of Engineering at the University of Kansas, a top regionaland a nationally ranked institution. Your communications, especiallywritten communications (composition, grammar, spelling, punctuation,etc), must reflect that status.

Do not send email in HTML format; it will notbe processed. Unless you are specificallyasked to send a document (in PDF format), sendtext-only emails in text-only format. See the Guidelinesfor Submitting Electronic Documents.

Video Discs

A number video discs on related topics (mostly from the DoD andNIST) have been obtained to show in the classroom (but onlyif time allows). Students are expected to take notes duringeach video presentation. Students with no background in theseareas are strongly encouraged to borrow these discs and viewat their convenient times.

Other Policies

In-Classroom Policy.Students are expected to come to the class on time, beattentive and engaged, conduct themselves very professionally,engage in informative discussion, and avoid anything that couldcause a distraction or detrimental either for other studentslearning or for the instructor's presentations.Profanity and swearing is not allowed.

Attendance Policy. Attendance is important and required. If a student misses a class session, he or she will be entirely responsible for learning the materials missed without the benefit of a private lecture on the instructor's part. Furthermore, the student will be responsible for finding out what assignments may have been given and when they are due. Exceptions will be made for family emergencies, religious observance, and illnesses. Verification of absence may be required.

Late Exam-Taking Policy. If a student will have to take an exam at a later time (due to an excused and verified absence), he or she will be asked to make the following pledge and sign it: I have been granted the priviledge to take this exam on _____________ because I was unable to take it on ______________ due to an excused absence. I pledge that I will not obtain from anyone by any means (in writing, speaking or via digital communications) any information about the exam.

Cell Phone Policy. Cell phones should be turned off before coming to the classroom. Cell phone use for the purposes of texting, email or other social media should be avoided. Earphones for music are OK during lab work or indiviualized problem solving, as long as the volume allows you to hear announcements. Also cell phone or other cameras may be used to photograph projects and the whiteboard but avoid shots that include the presenter.

Laptop Policy. It is OK to use laptops, tablets or similar devices for taking notes but turn off audio and avoid any possible uses (e.g., Web surfing or social media visits) that could cause distraction for others.

Accommodations for Students with Disabilities. The University of Kansas is committed to providing equal opportunity for participation in all programs, services and activities. Requests for special accommodations may be made thru the KU Student Access Services.

Academic Integrity/Dishonesty Policy. The definitions and consequences of institutional academic integrity policies will used. Academic dishonesty 'includes giving or receiving of unauthorized aid on examinations or in the preparation of assignments or reports, knowingly misrepresenting the source of any academic work, falsification of research results, and plagiarizing of another's work.'

Please take the KU Academic Integrity Quiz.

Incomplete Grade Policy. 'Incomplete (I) grades are used to note, temporarily, that students have been unable to complete a portion of the required course work during that semester due to circumstances beyond their control. Incomplete work must be completed and assigned an A-F or S/U grade within the time period prescribed by the course instructor. After one calendar year from the original grade due date, an Incomplete (I) grade will automatically convert to a grade of F or U, or the lapsed grade assigned by the course instructor.'

KU's Diversity Policy Statement. As a premier international research university, the University of Kansas is committed to an open, diverse and inclusive learning and working environment that nurtures the growth and development of all. KU holds steadfast in the belief that an array of values, interests, experiences, and intellectual and cultural viewpoints enrich learning and our workplace. The promotion of and support for a diverse and inclusive community of mutual respect require the engagement of the entire university.

KU's Sexual Harassment Policy. The University of Kansas prohibits sexual harassment and is committed to preventing, correcting, and disciplining incidents of unlawful harassment, including sexual harassment and sexual assault. Sexual harassment, sexual violence, and a hostile environment because of sex are forms of sex discrimination and should be reported. (“Sexual Harassment” means behavior, including physical contact, advances, and comments in person, through an intermediary, and/or via phone, text message, email, social media, or other electronic medium, that is unwelcome; based on sex or gender stereotypes; and is so severe, pervasive and objectively offensive that it has the purpose or effect of substantially interfering with a person’s academic performance, employment or equal opportunity to participate in or benefit from University programs or activities or by creating an intimidating, hostile or offensive working or educational environment.)

Commercial Note-Taking Ventures. Pursuant to the University of Kansas’ Policy on Commercial Note-Taking Ventures, commercial note-taking is not permitted in this course. Lecture notes and course materials may be taken for personal use, for the purpose of mastering the course material, and may not be sold to any person or entity in any form. Any student engaged in or contributing to the commercial exchange of notes or course materials will be subject to discipline, including academic misconduct charges, in accordance with University policy. Please note: note-taking provided by a student volunteer for a student with a disability, as a reasonable accommodation under the ADA, is not the same as commercial note-taking and is not covered under this policy.