Menu |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
 |
CIS 431
Database System Design and Management
Course Syllabus
Summer, 2005
REVISIONS:
5/26 8 AM, A03 REVISED
5/26 3:40 PM, A04 REVISED
6/1 8:55 PM, A04 REVISED (The Hint for MS Word applies to A03, also.)
6/3 1:10 AM Click here to view the Assignment 3 Solution in PDF format.
6/3 8:01 PM, A05 Clarified
this is a test
6/16 8:26 PM, A09 Part
of the assignment cancelled
6/17 12:24 PM, A11 Additional Attachment
Required
6/19 2:15 PM, A10 Clarification
6/22 11:22 AM, A11 Please put all SQL statements in your Word Doc
CLASS INFORMATION
Instructor: Mr. Art Hendela
Office:
Phone: 201-460-1253; ( if urgent, you can also leave a msg. for
Mr. Hendela at the CCS Secretary (973) 596-3366 )
E-Mail:
CIS431@Hendela.com (Please do NOT send CIS431 email to Mr.
Hendela's NJIT or main corporate account!)
Textbook: D. Kroenke, DATABASE PROCESSING: FUNDAMENTALS, DESIGN & IMPLEMENTATION, 10th Edition, Prentice-Hall, 2006. (Earlier editions of this text will NOT satisfy the course requirements - only purchase and use the 10th edition.)
Dr. Scher's Vertical Portal Page (HomeQuests Avenue): http://www.cis.njit.edu/~scher
Lecture Notes: Slides can be downloaded from Dr Scher’s website for CIS431
Course Grade Components:
· Midterm Exam (closed book) 32%
· Final Exam (open book) 33%
· Home Mini-Exams (conceptual assignments and DBMS projects) 30%
· Class Participation 5%
Policy on Collaboration/Cheating: Every assignment/project is a 'home-mini-exam.' Students found cheating/collaborating/plagiarizing will be immediately referred to the Dean of Students and the NJIT Committee on Professional Conduct and subject to Disciplinary Probation, a permanent marking on the record, possible dismissal and a grade of 'F' in the course. All submitted assignments are carefully checked for similarities, and plagiarism and guilty students will be identified.
Below are the TOPICs covered in the course, the related TEXTBOOK readings, and the Lectures to be attended. Remember one of the keys to success in CIS431 is your own self-discipline - your goal should be to maintain currency each day, and NEVER fall behind! (Note: this is a tentative schedule, and we reserve the privilege to modify and edit these topics for the benefit of the course.)
|
TOPIC |
TEXTBOOK, PRESENTATION VIDEOS AND VIDEOTAPES (VT) |
HOMEWORK |
DUE |
|
|
5/23 (1) |
Welcome, File Processing, Introduction to Database Concepts, Comparing Features |
Chapter 1 & Presentation Slides |
A01 | 5/25 |
|
5/25 (2) |
Entity Relationship Data Modeling (Chen E-R model, and Extended E-R model) |
Chapter 5, pp. 120-135 & Presentation Slides |
A02 | 5/26 |
|
5/26 (3) |
IDEF1X Data Modeling |
Appendix B & Presentation Slides |
A03 | 6/1 |
|
6/1 (4) |
Relational Database Design With MS Access, and Introduction to Semantic Object Data Models |
Appendix A, Presentation Slides |
A04 | 6/2 |
|
6/2 (5) |
Semantic Object Data Modeling, and Cool Strategy.com's Table Designer 1.0a |
Appendix E& Presentation Slides |
A05 | 6/6 |
|
6/6 (6) |
The Process for Designing Data Models, and The Relational Algebra for Query Design |
Chapter 3 & 4 & Presentation Slides |
A06 | 6/8 |
|
6/8 (7) |
Querying a Database With GQBE, and Translating an Extended ER Model into a Relational Design |
Chapter 6 Presentation Slides |
A07 | 6/13 |
|
6/9 |
Midterm Exam in class |
|
||
|
6/13 (8) |
Functional Dependencies and Decompositions |
Chapter 4, continued, Presentation Slides |
A08 | 6/15 |
|
6/15 (9) |
Database Normalization (1NF, 2NF, 3NF, BCNF, 4NF) |
Chapter 3 & Presentation Slides |
A09 | 6/16 |
|
6/16 (10) |
Domain Key Normal Form and Relational Design |
Chapter 3 & Presentation Slides |
A10 | 6/20 |
|
6/20 (11) |
Structured Query Language (SQL) |
Chapter 2& Presentation Slides |
A11 | 6/22 |
|
6/22 (12) |
Advanced SQL Design and Oracle |
Chapter 10 Presentation Slides |
A12 | 6/23 |
|
6/23 (13) |
Triggers, Active Databases, and Using SQL in Applications, and Concurrency Control |
Chapter 7 & 9 Presentation Slides |
Study for your Exam. Good Luck ! | |
|
6/27
|
Comprehensive Final Examination Open Book/Open Notes |
|
Case Study for CIS 431, Summer, 2005
Class Instructions:
Each homework will be done in MS Word and sent as an attachment to:
The Subject of the email MUST be in the following format or NO CREDIT WILL BE GIVEN. The format is as follows:
Ann, LastName, FirstName
Where Ann is the Assignment number listed after the date, and the Lastname, FirstName is your Last and First name as listed in my course directory. DO NOT Change it after your first submission.
Example for the assignment of 5/23 (A01)
A01 Hendela, Arthur
IMPORTANT: Place your name, assignment number, date, and page number in the Header of your attached Word document so that it appears on all pages.
Attachments must be named as follows:
Worddocs: Ann_LASTNAME_FIRSTNAME.DOC
Access files: Ann_LASTNAME_FIRSTNAME.MDB
Case Study Background
Vamkpharm Pharmaceuticals is a research driven, pharmaceutical company based in New Jersey with offices, research centers, and manufacturing plants throughout the country. All aspects of a pharmaceutical company are regulated by the Food and Drug Administration (FDA – www.fda.gov). Vamkpharm has asked you to consult on the development of a training database system that will be compliant with 21 CFR Part 11, the regulation that governs electronic record keeping.
Throughout this semester, you will develop your proposal, recommendations, and prototype for the implementation of your training database system. You will include resource estimates and how much it will cost. The resource time is the number of hours it will take for planning, development, and implementation. Your cost estimate will be the number of hours times the amount you bill per hour.
As with all consulting customers, I reserve the right to make changes to the assignments. I will post a change date on the above table. It is your responsibility to check this page for any changes.
Homework/Mini-Exams
5/23 (A01)
BACK TO TOP OF TABLE
Your client has some in-house technical people who believe that they know all about database management systems. Because of time constraints, they cannot perform the actual development, and cannot actually participate in the technology selection process more than just enough to provide some guidance. To implement this multi-location database system, VamkPharm has asked that you limit your selection of databases to one of the following:
DB2, ORACLE, MS SQLSERVER, EXCEL, VISUAL FOXPRO, MS ACCESS.
Develop bullet items on the pros and cons of each of these choices. I want five pros and five cons in the following format. Cite any references and website you use for your sources.
For example:
|
ITEM |
DATABASE |
PROS |
CONS |
|
1 |
DB2 (mainframe) |
1. Can store massive amounts of data. 2. Highly scalable security |
1. Runs on mainframe 2. Expensive |
|
2 |
ORACLE |
|
|
|
3 |
MS SQLSERVER |
|
|
|
4 |
EXCEL |
|
|
|
5 |
VISUAL FOXPRO |
|
|
|
6 |
MS ACCESS |
|
|
You may use the two freebies I've given you for DB2.
DUE: 5/25
5/25 (A02)
BACK TO TOP OF TABLE
The customer would like to know a little about the candidate consultant on a personal level. Please provide the following information to the main consulting office for presentation to the client:
Name:
Year in School:
Interests:
Job Experience/Who you work for:
Database experience
Programming Experience
What you'd like to get out of this course:
DUE: 5/26
5/26 (A03)
BACK TO TOP OF TABLE
The president of the company does not understand computer technology at all. Certainly, he does not understand database technology. Presidents and managers can understand simple diagrams if they are well explained. These are the requirements that you need to look at and turn into an E-R diagram.
The users of the system will be required to be tracked and given some security rights. Depending on their SECURITY clearance the users will be given access to certain PROGRAMS within the system. Some users will be able to write data to all areas of the database. Some users will be able to run all programs. VamkPharm tracks costs within EXPENSE_CENTERS. Every time that a course is given, costs must be tracked.
When a COURSE has an OFFERING, a TRAINER must be booked.
Produce an Entity-Relationship diagram for the relationships between the data that are given above. Also, produce the same diagram using IDEF1X diagram notation.
NOTE: Yes, I know that the requirements are sketchy, and there isn't enough information to make a good decision at this point. Make the diagram as best at you can and list any assumptions that you needed to make to create it.
PLEASE, DO NOT WORRY IF YOU THINK YOU ARE WRONG. Go with it. Assumptions cannot be wrong, but unstated assumptions certainly are wrong!!!
REVISED 5/26 8 AM: Create an estimate with how long it would take you to create these diagrams. Include an hourly rate for your service. Give the total cost (Total Hours x Rate)
LOAD Access. Load Table Designer onto your PC's. If you do not have MS Access loaded on your PC or Laptop, please obtain it from the school license location discussed in class (distrib.njit.edu). Run the setup. If you already have Access, skip this one.
Download and run the setup to install Table Designer, Academic Edition. You may obtain it from Cool Strategy's website:
-
Fill out the form “Coolstrategy.com Tabledesigner Software Download”
-
On the next page, click "Download TableDesigner Software" (About 9 Meg)
-
Save the TDACADEMIC.EXE to a temporary folder
-
Double click the TDACADEMIC.EXE to install the software (About 26 Meg).
END REVISION 5/16 8 AM
DUE: 6/1
6/1 (A04)
BACK TO TOP OF TABLE
Each COURSE that you will be offering will be offered by one and only one DEPARTMENT at one and only one LOCATION.
REVISION: 05/26 3:40 PM
Add these tables to your system diagram. What information would you like to track for each course? For each Attendee?
END REVISION 5/26
Think about what would make a course offering unique. This will be a combination of fields, definitely, more than one. HINT: How many places can one instructor be at one time? If the course is done on video tape, can an instructor be in more than one place at the same time, or almost so? What would be the tie breaking field? Explain. List any assumptions.
REVISION: 06/01 8:55 PM
IMPORTANT: You only have to update your E-R Diagram, NOT your IDEF1X Diagram.
HINT: For use with MS Word, Use the AutoShapes found on the DRAWING Toolbar. To enable the Drawing Toolbar, Right Click on the line File/Edit/View, etc. Click on "Drawing". Many of the symbols are in the Flowchart section of AutoShapes.
END REVISION 06/01 8:55 PM
DUE: 6/2
6/2 (A05)
BACK TO TOP OF TABLE
Use Table designer to create your initial table design based on your feedback.
Redo your E-R diagram from Assignment A03 as a Semantic Object Model.Explain the differences between the two approaches.
REVISION 6/3 8:01 PM
You should be attaching two files to your email. One with the APM File for Table Designer. That's your SOM Diagram. The other will be a Word Document with an explanation of the two approaches, E-R and SOM. Please remember to use the Standard header with Ann LastName FirstName Date. You file names should be A05_yourLastName_yourFirstname.Doc or .APM. Write if you have questions.
END REVISION 6/3 8:01 PM
DUE: 6/6
6/6 (A06)
The management of VamkPharm Pharmaceuitcals is becoming very confused by all of these different types of diagrams, with so many many different symbols. Produce a list of the symbols that are used in the E-R Diagrams, Semantic Object Models, and IDEF1X notations. Explain the meaning of each using a bulleted list.
Create a table with the relational operators. Use Word's Insert Symbol to find the Greek letters. List their meaning in a second column.
DUE: 6/8
6/8 (A07)The training database must show the attendance of each employee at a course. For an OFFERING, there is a set of data that is common for all attendees. For example, all people in this course will be attending the class on Monday, Wednesday, and Thursday from 6 PM to 9 PM at Kupfrian Hall room 106. The class is sponsored by the CIS department.
DETAILS, that is, what is covered in the course will vary from night to night. In the terms of the pharmaceutical industry, certain Good Manufacturing Practices must be made into Standard Operating Procedures (SOPs). Once created, the SOPs will be revised and employees will need to be retrained.
ATTENDEES will, of course, need to be entered into the proposed system. What fields will you need to track.
Some of the relationships between the tables are weak. Which ones are they and why?
Create an E-R diagram to represent the relationships between these tables.
Due: 6/13
6/13 (A08)
The data for the system includes the following items:
Course Number, Date, Time, Trainer, Employee Number, Employee Name, Course Name, SOP Number, SOP Effective Date, SOP Name, Department giving the course.
Break this set into Super Keys, Candidate Keys, and Primary keys.
Show a preliminary breakdown of how the data is organized into tables.
DUE: 6/15
6/15 (A09)
The time is getting close for VamkPharm to be given a training database model.
6/16 8:26 PM, A09 Part of the assignment cancelled
Since we did A08 together, you do not have to do this part. The performance impacts are still required
Use the data from A08 to create a data model that is in BCNF and 4NF. Show the breakdown steps using 1NF, 2NF, and 3NF.
6/16 8:26 PM, END
What else should you consideration in the design of your database in terms of performance of the overall system? Create a bulleted list of all impacts. Consider that any system that you design will be on a computer that has certain processor speeds, RAM, etc, and will be on a network. In a second column list an impact of High, Medium, or Low impact effect.
DUE: 6/16
6/16 (A10)
Model your tables in DKNF form.
ESSAY: When is it appropriate to denormalize the data? Give specific reasons why it is sometimes desirable to do this. When would pharmaceutical regulators require this?
What should you add to track changes in the database? What impact will this have on performance?
6/19 2:15 PM, A10 Clarification BEGIN
First, take the final diagram that we produced
from the other night A09 and put it into DKNF Form. For the most part
this will mean that you are adding the domain definitions such as the
physical attributes Char(25) or whatever the size you are making them.
Part 2. In about 1 page, please tell me when it is appropriate for
Denormalizing. I went over this during my lecture the other night. Think
about reproducing reports as they were in the past.
What would you need to see if you needed to reproduce history? Hint:
Auditors....
6/19 2:15 PM, A10
Clarification END
DUE 6/20
BACK TO TOP OF TABLE
6/20 (A11)
6/22 11:22 AM, A11 Please put all SQL statements in your Word Doc
At this point, you will have created the tables for your training database. Now you need to start entering some information. Create a text file with the EMPLOYEE data. This file will have 26 records. Make up the employee numbers. Make sure that the last names begin with each letter of the alphabet except "F". Once this is completed write the SQL to do the following:
1) Use Access to import the data into the table. Discuss any problems that you encounter.
2) Write the SQL INSERT statement to add some extra records.
3) Write the SQL DELETE statement to remove all records that have last names beginning with the letters A, H, and S
4) Write the SQL to create a new temporary table with the same structure as your EMPLOYEE table.
5) Write the SQL INSERT to copy the records to the new table.
6) Write the SQL select statement or statements to determine the number of records in the original and temporary tables. What statement would you use to ensure that there are no duplicate rows. Write the statement and state your results.
7) Write the SQL to destroy the temporary table. What happens to the data in the table?
8) From your original table, please find the total number of records that have last names beginning with A, B, and C.
9) Change the size of your Last name field to be 10 characters longer. What effect wil this have on performance of the database?
Show all SQL in your report. Attach your MDB files. Make sure you use constraints on tables to ensure uniqueness.
6/17 12:24 PM, A11 Attach your Import Text file
DUE: 6/22
6/22 (A12)
Enter some data into your COURSE table and perform the following queries:
1) Find all of the students that have taken classes in the range 5/23 to 6/27, 2005 whose last names begin with S, T, or V. Write the SQL using a join and again as a sub-select.
2) What characteristics make tables able to use UNION?
3) Add a column to your COURSE table for a numeric grade. Populate the column with a numeric grade between 1.0 and 4.0. Answer the following:
a) How many students have at least one grade of 4.0?
b) How many students have never gotten a 4.0 in a course? List the names.
Show all SQL. Attach your MDB files.
DUE: 6/23
PowerPoint is a registered trademark of
Microsoft Corporation.
ALL RIGHTS RESERVED
Last Updated: 6/17/2005