SYLLABUS -- Fall 2017
COURSE NO. & TITLE: BMIS 441 Data Base Management
PROFESSOR: Dr. Jason Chen
E-MAIL ADDRESS:
Blackboard: https://learn.gonzaga.edu/
DAYS, TIMES & ROOM: Sec. 01: T,R 10:50-12:05 p.m., Jepson Center 012
Sec. 02: T,R 3:15-4:30 p.m., Jepson Center 012
OFFICE: Jepson Center 259
OFFICE HOURS: T,R: 4:30-5:30 pm, W: 4:00-:5:00 pm; others by appointment
PHONE: (509)313-3421
REQUIRED TEXTS 1. Essentials of Database Management, 1st Edition by Hoffer, Topi and Ramesh, Pearson, 2014; ISBN-13: 978-0-13-340568-2
2. Oracle 12c SQL, Joan Casteel, 2nd Ed., Course Technology, 2016; ISBN: 978-1-305-25103-8
COURSE DESCRIPTION AND GOALS: The purpose of the course is to introduce students the major concepts and theories of database management, including aspects of entity-relationship (E/R) model, normalization, project life cycle, system models, data warehouse and data administration. Another portion of the course will apply concepts, models, and technology to develop business applications using a state-of-the-art relational database management system of Oracle. We will spend a significant portion of class learning Structured Query Language (SQL), the industry standard language for interacting with relational database management systems.
To improve your learning effectiveness, Oracle 12c can be downloaded free (www.oracle.com) and you may use it to practice Oracle at home. However, you are required to complete the homework assignments at Jepson Lab in order to fully experiment the CLIENT/SERVER environment – that is your benefits!!
CLASS POLICY: Students are expected to study the assigned topics before attending the class and to participate actively in class activities and sometimes prepare for pop quizzes. You will be assigned as a member in working on the group projects. Use of a word processor for papers/reports and assignments is required. It is your responsibility to keep all the returned materials. In the event of a disputed entry on the instructor's grade sheet, the student MUST show the material(s) to have the grade(s) changed.
· Student can be penalized for up to 5% of the total points on the grade for distracting behavior each time in class (i.e. talking, reading newspaper, cell phone is ringing, internet surfing, passing notes, etc.)
GRADE POLICY ON SOFTWARE ASSIGNMENTS/LECTURES: If you violate dishonest policy on any assignments, you will receive an F on the course grade (you are allowed to discuss with other students, however, you should do your own work and responsible for fully understanding the work you turned in). You may be selected randomly to explain your assignments to the instructor and if you are unable to explain it, you violate the academic honesty policy stated below. An F will be assigned for final course grade if you receive a zero on any computer (Oracle related) assignment. However, you might be waived from the “F” policy, if you receive permission in advance from the instructor or provide any university-approved excuse.
· E-MAIL and Blackboard/Web POLICY: It is your responsibility to check your E-MAIL and blackboard/web site daily. There might have several important messages sent to you via the E-MAIL and the blackboard so that all of students will keep class information up-to-dated (and information from your friends) and step toward the information age.
· E-mail Communication – All e-mail communications with me should bear the course and section number (viz: BMIS441-01 or BMIS441-02) in the subject line, without which the e-mail is likely to be unread. Furthermore, you should include “Dr. Chen” and “your full name” at the beginning and end of the content of each email respectively.
· EXAMS: Midterm exams will include only that material covered since the previous exam. The final exam is comprehensive. This includes material from the textbooks, software packages, and class discussions -- all can appear on the exams. The type of exam will be left up to the instructor. It may be objective, essay, or a case problem. Your instructor will specify the format before the exam. No credit will be received if you miss an exam or quiz unless you have permission from the instructor or obtain a University-approved absence.
· Academic Honesty Policy: The academic honesty policy in the Gonzaga University’s student handbook (p. 145) states that dishonest activities such as cheating, fabrication and plagiarism carry penalties that could lead to severe penalties. Should you have any doubt regarding a course of conduct, don’t hesitate to inquire of me for guidance on addressing the situation. Furthermore, all class assignments (everything) you turned in must be “Original Work” for this class only” – it means that they are not from someone else or from your other class this semester or before and is considered a violation of “Honesty Policy”.
QUIZZES: You will take a quiz for each chapter (main text) during a designated period. These quizzes will contain true-false and/or multiple choice questions. Each quiz normally has 11 questions (with 1 point bonus) and you should complete it within the time limit (usually less than 10 minutes) on midnight Sunday. One exception is that chapter 9 quiz is worth 60 points (total possible points of 63 points with 3 points bonus) and the time limit is 20 minutes. There will be 9 quizzes this semester. Quizzes are available under ‘Tests & Quizzes’ button on the Bb. The rules of taking tests/quizzes are:
(1) Students are NOT allowed to take this assessment multiple times. Therefore, you should use a computer with reliable/stable internet connection. To be fair to all students and with the nature of online quiz (and you have all control at your end), a zero point will be assigned if you lost Internet connection.
(2) Students must complete all online assessments the first time they are launched (including exams)
(3) Time limit: 10 minutes for every quiz.
(4) There will be 1 point off for every 15 seconds of exceeding specified time limit.
(5) Test will be saved and submitted automatically when time expires.
COURSE ATTENDANCE: Students are expected to attend the class on time. The instructor is aware that absence might be inevitable when emergency arises. Under such a circumstance, an absence is allowed through the whole semester without penalty. However, If you miss a class session without what I consider a legitimate excuse (be sure that you should have my permission in advance or present a university-approved evidence after the missed class), I reserve the right to take 10 points off from the class performance for each class you missed. The more classes you missed the more points will be taken off. You must contact the instructor about every absence.
Furthermore, students should be aware of the University policy regarding absence. According to the university policy, "[T]he grade given for excessive absence is V, which has the same effect as F and is counted in the GPA....The fact that a student has met other course requirements (such as papers) is not sufficient to change a V to a passing grade." (Catalogue, p. 38). The total time of excessive absence in this class is 300 minutes, equivalent to four absences. In other words, if one student misses four classes for whatever reasons, he/she should expect a V for his/her final grade.
DREAM Students: If you are in the DREAM (Disability Resources, Education and Access) program, it is your responsibility to inform the instructor one week in advance and contact DREM office for arranging EVERY test in their office so that students’ testing schedule can be arranged
FINAL EXAM SCHEDULE:
TR 10:50 am class - Wednesday, December 13; 2017, 3:30 pm to 5:30 pm
TR 3:15 pm class - Thursday, December 14; 2017, 3:30 pm to 5:30 pm
GRADING POLICY (tentative):
Quizzes (9 @ 10 ) …………...... 90
Pop Quizzes (1-3) ...... …….. 10-30
Written Assignments (5 @ 5) …………………………………………… 25
Computer (Oracle) Assignments (5@9, 8) ……………………….………… 53
Mini Project - MVC_Hospital (8, 8, 20) …………………………… 36
Midterm exams (150, 150) ...... 300
Team (Group) Project (with SDLC and Oracle Features) …….……….. 80
Final Exam (Comprehensive) ...... 200
Class attitude, performance and attendance ...... 32
------
Total points 836-856
Please note that if you receive a zero on computer (Oracle related) assignments, your final course grade is F.
GRADE RANGES:
A / 94% and above / A- / 91% / B+ / 88% / B / 85%B- / 82% / C+ / 79% / C / 75% / C- / 71%
D+ / 68% / D / 65%
BMIS441 - TENTATIVE SCHEDULE (8/20/2017)
[Week] Date /Topic and Activities
/Homework Assigned
(Due on the next class)[1]
Aug. 29 / Course Intro.
(According to the university policy you MUST drop the class if you do not attend the class today)
Oracle (#1): Overview of Database Concepts
Logon to Blackboard at https://learn.gonzaga.edu/ and check all course information/materials / HANDOUT – Oracle_ch1_out.pdf
Handout to students
Aug. 31 / Oracle (#1): Overview of Database Concepts (conti.) / Oracle(#3,5,10 on p.21)
[2]
Sep. 5 / Ch.1: DataBase Environment and Development Process / Handout:
2a. Customers_Orders (JLDB_Referential_Integrity)-out (class exercise) – reenforce
3. JustLee_DDL(Organized_With Order of Entering Data)-out (HW)
#8 (p.40)
Sep. 7 / Ch.1: DataBase Environment and Development Process (cont.)
Oracle (#2): Logon to Oracle12c / #12-a (p.40)
Online quiz#1-ch.1(Sunday evening)
[3]
Sep. 12 / Oracle (#2): Basic SQL SELECT Statements
Bring a “Flash Drive” / TWO handouts -
1) syntax of SQL commands
2) JustLee_DDL(Original_With Order of Entering Data)-out
Oracle#2: Q#1,3 (p.54)
Sep. 14 / Oracle (#2) – cont. / Oracle#2: (see ppt); Spooled file name:
Oracle_ch2_Spool_Lname_Fname.txt
[4]
Sep. 19 / Oracle (#3): Table Creation and Management
JLDB_Referential_Integrity / Handout: CUSTOMERS and ORDERS w/ referential integrity
Oracle#3: (see ppt);Spooled file name:
Oracle_ch3_Spool_Lname_Fname.txt
JustLee (pk&fk)
Sep. 21 / Oracle (#4): Constraints / Two handouts for chapter 4 (Oracle)
1) JLDB_DDL (final schema) PK, FK
2) JLDB_Tables_extra
Oracle_ch4_Spool_Lname_Fname.txt
[5]
Sep 26 / Oracle (#7): User Creation and Management
Oracle (#5): Data Manipulation and Transaction Control [need/prepare NW database] / Oracle_ch5_Spool_Lname_Fname.txt
[correction on p.138: Name Datatype should be VARCHAR2(5)]
Sep. 28 / Oracle(#8): Restricting Rows and Sorting Data
Oracle(#9): Joining Data from Multiple Table (p.296-310; p.326-344 [set operators]) / Handouts:
1) Oracle_ch08 (GROUP BY & HAVING)-out1)
2) JLDB_ch9 (additional database)
3) Northwood DDL
Oracle_ch8_Spool_Lname_Fname.txt
Oracle_ch9_Spool_Lname_Fname.docx
[6]
Oct. 3 / Oracle(#9): Joining Data from Multiple Table (cont.)
Oracle(Basic): Basic Nested Queries and Views
Groupings/BNQV handout / Handouts:
1) JLDB_ch9 (additional database)
2) Northwood DDL
3) Oracle_ch11_BNQV-out (Basic Nested Queries and Views
Oracle_ch9_Spool_Lname_Fname.docx
Oct. 5 / Oracle(#11): Group Functions (up to p.424)
Study/Review for Exam-I and Project / Oracle#11: (see ppt); Spooled file name:
Oracle_ch11_Spool_Lname_Fname.txt
[7]
Oct. 10 / EXAM- I (ch. 1 and Oracle chs. 1-5;7-9 and 11)
Oct. 13 / Ch.2: Modeling data in the organization / MVC mini-project (to be assigned)
[8]
Oct. 17 / Ch.2: Modeling data in the organization (continued) and MVC mini-case discussion / Online quiz#2- ch.2 (Sunday evening)
.
Oct. 19 / Ch.3: Enhanced E/R Model / MVC Part I (due this Sunday):
MVC_Hospital_ER_Lname_Fname.doc
[9]
Oct. 24 / Ch.3: Enhanced E/R Model (cont.) and MVC mini-case discussion
Ch.6: SQL (study yourself) / Online quizzes ch.3&6 (Sunday evening)
Start work on MVC script file (Part II)
It is very time-consuming start earlier
Oct. 26 / Ch.4: Logical DataBase Design (p.121-131) / Revise:
MVC_Hospital_ER_Lname_Fname_v2.docx
MVC Part II (due this Sunday):
MVC_Hospital_Lname_Fname.sql
[10]
Oct. 31 / Ch.4: Logical DataBase Design (p.131-144) / Project Proposal Due (Phase I – topic and description – see end of syllabus for details.
Start work on MVC Query
Nov. 2 / Ch.4: Logical DataBase Design (p.144-156) / Online quiz ch4 (Sunday evening)
MVC Part III (due this Sunday):
MVC_Hospital_Complete_L_F.doc
(both soft and hard copies)
[11]
Nov. 7 / Oracle(#10): PL/SQL Oracle(#10): PL/SQL and Selected Single-Row Functions (Part II - study yourself and only those functions listed on the ppt) / Start work on Oracle#10: (see ppt);
Nov. 9 / Oracle(#10) – cont.
Oracle(#12): Subqueries (NO MERGE) Statements / Spooled file name: Oracle_ch10_Spool_Lname_Fname.txt
Bonus: Oracle#12: (see ppt); Spooled file name:
Oracle_ch12_Spool_Lname_Fname.txt
[12]
Nov. 14 / Oracle (#6): Additional Database Objects (CREATE SEQUENCE and QUERY OPTIMIZATION with INDEX) / Project Due (Phase II – E/R Design)
Oracle#6: (see ppt); Spooled file name:
Oracle_ch6_Spool_Lname_Fname.txt
Nov. 16 / Oracle(#13): Views
Ch7: Advanced SQL (study yourself)
Review and/or Slack time
The World is Flat (Thomas Friedman)
Review for EXAM II / Oracle#13: (see ppt); Spooled file name:
Oracle_ch13_Spool_Lname_Fname.txt
Online quiz ch.7 (Sunday evening)
[12/13]
*Nov. 21 / *Exam-II
(Chapters 2,3,4,5,6,7; Oracle 6,10,12,13)
Nov. 23 / ***Nov. 22-24 Thanksgiving Holiday (NO Class)
[14]
Nov. 28 / Oracle (other topics) – review and reinforce
Project – review and discussion
Ch.9: Data Warehousing / Revise your project
Nov. 30 / Ch.9: Data Warehousing (cont.) / Revise your project
Project Due (Phase III – Physical Design and Oracle Implementation
Online quiz ch.9 (Sunday evening)
[15]
Dec. 5 / Group Presentation / Project Due (Phase IV - Complete) ALL GROUPS
Dec. 7 / Group Presentation (continued)
Dec. 12 -
Dec. 15 / FINAL EXAM
Ch. #: Essentials of Database Management, 1st ed., by Hoffer et.al., Pearson
Oracle: Oracle 12c SQL, Casteel
PROJECT PROGRESS REPORT
Phase I: Your group may either select an appropriate topic from a reality (strongly recommended) or make up a business scenario. Turn in a written report (at least 2 pages) includes project’s description (scenario) and its objectives (what you try to accomplish and how) to get approved from the instructor. Please check sample copies on the Blackboard (available at “Assignments & Projects).
Phase II: Extend the content on Phase I and add base (E/R) model (or more) to your existing report. At least 4 pages.
PROJECT’S FORMAT and CONTENTS
The final project written report should include the following (but not limited to):
1. Project scenario. (what is the problem domain?)
2. Project objective. (what goals your group try to accomplish and suggest?)
3. What and how were they accomplished?
They should include logical (E/R) model, a well-defined SDLC and Oracle implementation. Furthermore, the detailed process for the “transformation of relations” and “normalization (up to 3NF)” should be stated in the report and powerpoint file.
4 Include 7-9 queries (including description of the queries and SQL solutions like you did in the MVC-case). The following requirements should be included in each query: a) View, b) Multiple Tables, c) Sub-query, d) SET operator, and e) Sequence and more to demonstrate your understanding/learning from the class lectures.