Assignment 1

IT 468 DB @ ITAM

- 1 -

Information Technology and Administrative Management Department (ITAM)

http://www.cwu.edu/it-management/

IT 468: Projects in Database – 4 credits

Summer 2016 Quarter

Online June 20 through August 19

Dr. Fen Wang Associate Professor

Office: Shaw-Smyser 231

Phone: 509.963.1444

E-Mail: [email protected] The best and quickest way to get answers to questions is

sending me e-mails through your CWU email account.

Note: emails sent over the weekend or holidays will

normally be processed the following work day.

Course website: https://canvas.cwu.edu/

All course related materials (lecture notes, assignments, forums etc.) will be posted on the course website.

All weekly assignments and team project deliverables are to be submitted via the Canvas course website.

Course description: This course is designed to provide students with a complete introduction to database concepts and the

relational database model, the single most important standard in database processing today. The primary

emphasis is on the design and development of the database component of modern information systems, and

on the management of the data resource.

Introduction:

This database systems course is targeted to the IT students who have acquired some basic data management

knowledge in lower level courses. Topics to be covered include QBE, SQL, normalization, design

methodology, DBMS functions, database administration, and other database management approaches, such

as client/server databases, object-oriented databases, and data warehouses. At the completion of this course,

students should be able to understand a user’s database requirements and translate those requirements into a

valid database design. Students are expected to actively participate in the discussion and practice of

materials covered. This course will be conducted online and students are expected to study the lecture notes

posted for each module timely and accomplish weekly assignments by due dates throughout the quarter.

Objectives:

Upon completion of this course, students should be able:

 To identify the nature, characteristics, and components of DB

 To explain the scope of DB design and advantages of the DB approach

 To write table queries and establish referential integrity using QBE/SQL

 To draw an E-R diagram to represent common business situations

 To transform an E-R diagram to a logically equivalent set of relations

 To translate a relational data model into efficient database structure

 To examine the database administration features provided by a DBMS

 To understand the DBA’s administrative and technical responsibilities

 To survey other advanced database management approaches

IT 468 DB @ ITAM

- 2 -

Required Textbook:

Database Concepts, 7/E, by David Kroenke and David Auer, Prentice Hall, 2015. ISBN: 9780133544626

Some material from the book will be covered in depth while some will be treated more lightly.

Additional material and resources will be posted on the course Website.

Reference Book:

Database Processing: Fundamentals, Design, and Implementation, 13th Edition by David Kroenke

and David Auer. Prentice Hall, 2014. ISBN: 9780133058352

Course Tools:

To complete the course you will need to draw E-R diagrams, create DB tables and write DB queries. We

will be using MS Access and MS Visio, as well as illustrating the other DBMS packages such as MS SQL

Server, Oracle, and/or MySQL.

Note: You can use the PCs at school (e.g., computer labs on second floor of Shaw-Smyser on Ellensburg

campus) and/or download the trial versions of MS Visio as needed to practice and work on assignments

and team projects during the quarter (check the course website for more details).

Grading Policy:

In this class you will be graded on weekly homework assignments, weekly quizzes, and one team project.

These grading instruments will have the following weight distribution:

Grading Instrument Grading System

Concept Quiz (weekly module quiz 1~9) 27% >93: A 90~93: A-

Assignments (weekly module assign. 1~9) 45% 87~89: B+ 83~86: B

Team Project (w/ 4 written deliverables) 28% 80~82: B- 77~79: C+

TOTAL 100% 73~76: C 70~72: C-

60~69: D <60: F

Concept Check Quiz: Every module contains a Concept Check Quiz covering the lecture notes and text chapter(s) in the module

and any related readings assigned. Each quiz accounts for 30 points (3% of the final grade) and all together

the quizzes account for 27% of the final grade. You can take the quizzes for any chapter at any time up to 3

times; however, you cannot take a quiz past the weekly module deadline. The link will disappear and

cannot be reopened.

Assignments: All homework is to be completed individually (account for 45% of your final grade). Technology mishaps

and access availability is NOT an excuse for a late assignment when you have almost a week to complete

each module. Please account in your timeline for Bb glitches, outages, and technology frustrations which

inevitably happen to all of us. Always SAVE and backup all of your work BEFORE you post it on Bb!

While I encourage collaboration among your peers, please do not discuss potential solutions. Please refer to

CWU policy on cheating (specified in Student Judicial Code) for more information.

Late Policy: All homework is to be submitted via Blackboard or through CWU email by the due time as

specified. It is always better to turn in your current work for partial credit than turn in nothing. As a “grace

period”, once during the course you can turn in a homework assignment one day after it is due with an

accompanying reduction of 20% of the earned grade. When it is not in on time the 2nd time or thereafter,

late homework will not be accepted.

Team Project: You are hired as a DB expert to help Garden Glory on its DB project. Garden Glory is a partnership that

provides gardening and yard maintenance services to individuals and organizations. The purpose of the term project is to reinforce understanding and application of key DB concepts and techniques and use a

structured approach to DB development in a team setting (about 6 students per team). Each student should

actively participate in the project. Each team member should plan to spend 20-40 hours total on the term

project. You are required to submit several written deliverables during the database design and

development process throughout the course.

IT 468 DB @ ITAM

- 3 -

You can find detailed requirements of the deliverables in the Project Deliverables folder on Blackboard

and/or in the “GARDEN GLORY” project section at the end of each chapter. Below are some additional

notes to help you go through the process smoothly.

 D1: DB Project Analysis & Query: Develop a reasonable and realistic plan and outline the steps to accomplish the integrated project by the end of the course. Explain why you choose a DB

approach for Garden Glory (GG) and what are the modification problems with a possible

spreadsheet file, make initial normalization suggestions regarding the DB design, and query the

trial DB to retrieve business-relevant data. You can also refer to project questions in §1~3 of the

Textbook.

 D2: DB Modeling & Design: You have gained a clear idea about the Garden Glory business and data requirements by conducting an initial analysis and query. Now analyze the business needs

thoroughly and create a conceptual Entity Relationship (E-R) model for Garden Glory to represent

the entities and relationships involved. Document the entity list, business rules and other

processing results as appropriate. You can also refer to project questions in §4 of the Textbook.

 D3: DB Specification & Implementation: After you have developed the conceptual E-R model for GG, convert that data model into a relational database design for Garden Glory. Then develop

the specifications for database implementation, indicating data types, PKs, and FKs. Implement

the design and create the GG Database and execute a variety of queries to test the functionality of

your DB. Document the processing results accordingly. You can also refer to project questions in

§5.

 D4: DB Management & Maintenance: Describe the nature of DB administration activities at Garden Glory and define the security scheme (using Figure 6-18 as a guide to describe the users,

roles, and permissions etc). Discuss the proper procedures for DB backup and recovery at Garden

Glory. You can also refer to project questions in §6.

Part of the grade for the team project will be based on the team’s work on deliverables D1 through D4 (60

points each), and part of the grade will be based on the team peer-evaluation results (40 points). Each

student will be given the opportunity to evaluate each other’s contribution to the team project.

Re-grading assignments/quizzes: The instructor is always willing to clarify what is required for a deliverable or why you received a

particular grade. Students may meet with me to get feedback regarding their performance throughout the

semester. While I will always correct mistakes in the arithmetic computation of grades and grade recording

errors, final letter grades are not negotiable and I will not entertain challenges to final letter grades.

Students with Disability: Students with disabilities who require academic adjustments in this class are encouraged to meet with me

during my office hours to discuss their disability-related needs. Please bring a copy of your Confirmation

of Eligibility for Academic Adjustments. We will then discuss how the approved adjustments will be

implemented in this class. Students without this, but in need of requesting services should contact the CDS

for additional information at Hogue 126, or via [email protected] or 963-2214.

Academic Integrity:

Academic integrity is an important value at CWU. By enrolling in this course, each student assumes the

responsibilities of an active participant in ITAM’s scholarly community in which everyone’s academic

work and behavior are held to the highest standards of honesty and integrity. Cheating, fabrication,

plagiarism, and helping others to commit these acts are all forms of academic dishonesty (please refer to

The CWU Student Judicial Code for more details). Academic misconduct will result in disciplinary action

that may include, but is not limited to, failure of the course, suspension or dismissal.

Tentative Schedule:

Below is a tentative schedule of lecture topics, readings, assignments, due dates, and other activities. The

instructor reserves the right to adjust this schedule for any reason, but will make every effort to advise

students of any changes well in advance.

IT 468 DB @ ITAM

- 4 -

Note: You can refer to the Calendar/Schedule table for more schedule related details on Canvas course

website, under the “Syllabus” folder.

Week/Dates *Topic Readings Assignment/Activity

1 6/20 ~ 6/24

Introduction to Class and DB

Management

Syllabus

& §1

Assigned to Group

Module 1 due (6/24)

2 6/27 ~ 7/1

The Relational Model and

Normalization §2

The Access Workbench 1

Module 2 due (7/1)

3 7/4-Ind. Day

7/5 ~ 7/8

Introduction to QBE and

Structured Query Language (SQL) §3

The Access Workbench 2

Module 3 due (7/8)

4

7/11 ~ 7/15

Introduction to QBE and

Structured Query Language (cont.)

D1: DB Analysis (7/13)

§3 The Access Workbench 3

Module 4 due (7/15)

5 7/18 ~ 7/22

Data Modeling and the Entity-

Relationship (E-R) Model §4 Module 5 due (7/22)

6

7/25 ~ 7/29

Data Modeling and the Entity-

Relationship (E-R) Model (cont.)

D2: DB Design (7/27)

§4 *MS Visio Tutorial

Module 6 due (7/29)

7 8/1 ~ 8/5

Transforming Data Models into

DB Design & DB Implementation §5

Module 7 due (8/5)

The Access Workbench 3-5

8 8/8 ~ 8/12

DB Implementation (cont.)

D3: Implementation (8/10) §5

Module 8 due (8/12)

The Access Workbench 6

9

8/15 ~ 8/19 Database Administration

Team Peer Evaluation (8/17) §6

Module 9 due (8/19)

Wrap-up & Finalize GG

group project

Final Project D4: DB Maintenance due by 11:59pm, Friday, 8/19/16

Note*: The content belonging to a topic is only partially presented on the table.