BARUCH COLLEGE-CUNY
ZICKLIN SCHOOL OF BUSINESS
Dr.
Abdullah Uz Tansel
Spring 2003
CIS 3400 Database Management Systems
Blackboard Log In Information: The first eight digits of your new Baruch
e-mail address e.g. (js012345@webmail.baruch.cuny.edu) is your username.
Your password is the PIN number that you received during registration.
Get your new Blackboard and WebMail Account
Please
click here for going to course
home page in Blackboard.
Course Logistics Course Description Textbook Course Outline
Prerequisite Assignments Term Project Grading
Miscellaneous Academic Integrity
Course
assistant:
You can see me during my office hours in Tuesdays and Thursdays. If you need to see me outside of the office hours please talk to me or send me email to determine a mutually good time to meet. You can reach me by email. In case you want to talk to me you can call (646) 312 3366. However, email is preferable for reaching me.
You are expected to visit this course web page frequently since it is a 'living document'. Before each class I will provide you notes that contain information about the assignments, term project, and lecture material. I will also post the assignments in this course web page.
PREREQUISITE
Prerequisites/corequisites: CIS 2200, CIS 3100
This course provides students with the background to design, implement and use database management systems in managing the data needs of an organization. It introduces, in a comparative framework, the structure, requirements, functions and evolution of database management systems. After covering conceptual data modeling and entity relationship data model the course focuses on relational data model. Students learn abstract languages such as relational algebra including their commercial implementations like SQL. Database design is introduced and discussed in detail. Concepts of data integrity, security, privacy, and concurrence control are introduced. Ethical issues in the maintenance and use of a database and globalization of information technology are also discussed. Students implement a major database application project using MS Access. Upon completion of the course students are expected to develop skills to:
o Understand the data requirements of contemporary organizations and how database management systems meet them,
o Develop conceptual data model specifications,
Required, (RB) P. Rob, Carlos M. Coronel, Database Management Systems Design Implementation & Management, Fifth Edition, Course Technologies, 2002.
Optional, (A) Joseph J. Adamski, C. Hommel, K. T. Finnegan, Microsoft Access 2000, Course Technologies,, Cambridge, MA, 2000. (This text is optional) You may obtain the tutorials from the Course Technologies web site: http://www.course.com/ Cost: about $45. You may obtain the tutorial data files from the Course technologies web site: http://www.course.com/.
Other references (In case you are interested in reading more about a topic that is listed below)
(BP) D. Baldwin, D. Paradice, Applications Development in Microsoft
Access, Course Technologies, Cambridge, MA, 1998.
(EN) R. Elmasri, S. K. Navathe, Fundamentals of Database Systems, Third Edition, Addison-Wesley, 2000.
(D) C. J. Date, Introduction to Database Systems, Volume 1, Addison-Wesley, Seventh Edition, 2000.
(DD) CJ Date, Hugh Darven, Guide to the SQL Standard, 4th edition, Addison Wesley.
(F) L. Friedrichsen, Data-Driven Web Sites with Microsoft Access 2000, Course Technologies, 2000
(K) K. L. Oxford, Microsoft Access 2000 with visual Basic for applications, Course Technologies, 2000.
(MS) J. Melton, A. R. Simon, Understanding the New SQL: A Complete Guide, Morgan Kaufmann Publishers, 1993
COURSE OUTLINE - Topics to be covered in
the course
|
CLASS |
TOPIC |
|
Jan. 28 |
File systems vs. Database Management Systems (DBMS), Evolution of DBMS and its Advantages, Data independence (Ch 1 in RC and Lecture notes). |
|
Jan. 30 |
Architecture of DBMS,. Schema, Subschema, Data definition and manipulation languages and Data dictionary/directories. Software as intellectual property (Ch 1, 2 in RC and Lecture notes) |
|
Feb. 4 |
Basic data characteristics, Entities, Relationships. Conceptual data modeling. Entity Relationship data model. (Ch 3 in RC) |
|
Feb. 6 |
Entity Relationship data model continued. (Ch 3 in RC, Start reading the book on MS Access, Tutorial 1, 2 in A) |
|
Feb. 11 |
No class, follow a Monday schedule |
|
Feb. 13 |
Relational data model, Domains, Existential and referential integrity constraints, Defining relations and enforcing integrity constraints in MS Access. Review Ch 2 in RC) |
|
Feb. 18 |
Relational algebra. (Lecture notes, for further reference on the relational algebra see the relevant chapters in D, and EN, Read Access Tutorial 3 in A). |
|
Feb. 20 |
Relational algebra continued, Introduction to SQL, Select statement (Ch 5 in RC and Lecture notes, Read Access Tutorial 4 in A). |
|
Feb. 25 |
SQL continued, nested select statement, Renaming relations in a Select statement (Ch 5 in RC and Lecture notes). |
|
Feb. 27 |
SQL continued, Exists condition and correlated queries, Aggregate functions, data maintenance operations, and bulk data loading (Ch 5 in RC and Lecture notes). Views in SQL (Lecture notes) |
|
March 4 |
SQL continued, Database application design and development, A case application, Forms, Reports (Ch 6 in RC, Tutorial 5 in A). |
|
March 6 |
Database application design and development, A case application, Forms, Reports (Ch 7 in RC). |
|
March 11 |
Test I |
|
March 13 |
Database application design and development, A case application, Forms, Reports (Ch 8 in RC, Tutorial 6 in A). |
|
March 18 |
Relational database design, Functional and keys multi-valued dependencies, Keys, Normal forms (1NF, 2NF, 3NF, and BCNF, Ch 4 in RC and lecture notes) |
|
March 20 |
Normal forms continued, Lossless-join decompositions and denormalization (Ch 4 in RC and lecture notes). |
|
March 25 |
Normal forms continued, Multi-valued dependencies, 4NF. (Ch 4 in RC and lecture notes). |
|
March 27 |
Translation from entity relationship model to the relational data model. (Ch 3 in RC, Tutorial 7 in A) |
|
April 1 |
Concurrent execution of transactions (Ch 9 in RC) |
|
April 3 |
Database administration, Database recovery and security (Ch 16 in RC). |
|
April 8 |
Accessing the database servers (Ch 12 in RC) |
|
April 10 |
Test II |
|
April 15 April 16-24 |
Follow a Wednesday schedule Spring Recess |
|
April 29 |
Introduction to Internet databases, Web
browsers as user interface, HTML, XML, and CGI (Ch 15 in RC, Tutorial 8 in A) |
|
May 1 |
Object oriented Databases (Ch 11 in RC) |
|
May 6 |
No class -Thanksgiving |
|
May 8 |
Object oriented query processing (Ch 11 in RC), Other data models, Hierarchical data model, IMS DL/I. Network data model. CODASYL approach, Schema and DML |
|
May 13 |
Traditional file structures, organization and access techniques. Sequential, Index Sequential. Direct (Hash) files, Secondary Keys, Inverted and Multiple linked list structures. |
|
May 15 |
Current trends in database technology, The data warehouse (Ch 13 in RC). , Electronic commerce and databases, Social implications of these trends and globalization of information technology, Ethical issues in database use and maintenance (Ch 14 in RC). |
|
|
|
|
Test I |
20% |
|
Test II |
20% |
|
Final Exam |
30% |
|
Assignments |
15% |
|
Term project |
15% |
|
TOTAL |
100% |
The mid-term exam will cover the topics we will discuss up to, but not including, the last class before the exam. The final exam will be semi-cumulative; it will cover some topics from the test I and II and all the topics covered thereafter. I will not ask questions about Microsoft Access in the exams. There will be mostly problem type questions and there may be a few essay questions too.
There will be two types of assignments: written and Ms Access assignments. Written assignments include problems about the topics we will discuss in class. You are required to hand in the your solutions on the due date. You can expect 4-5 such assignments. There will be about 2-4 Ms Access assignments. For the problems given in the assignment you will prepare your solutions in Ms Access and hand in your solution in hard copy or on a disk.
Homework assignments are due on the day specified for handing in the assignment. Assignments handed in one or two class sessions after the due date will loose 10% and 20%, respectively. Beyond these dates, the late penalty is 40%. The assignments are very important and I expect you complete them in any case.
The course requires a term project, which involves designing and implementing a database system for an organization. Students will work in teams, 3 members or less, for the term project. If you prefer you can work on your project individually. Students are expected to form their teams in the first two weeks of the term and select a project topic by the end of the third week. The term project involves several steps. You have to complete these steps in order since a step builds on the result of a prior step. The final project report includes all the steps and due the last class in the term. Pleas give me a hard copy report preferably with a diskette.
The term project will be implemented by using Microsoft Access. Microsoft Access is a use-friendly database implementation tool. There are also many books available for self-study. I have included a list of books on Ms Access above. Term project details follow:
The project requires building a database application for a real-world scenario of your choosing. You will design schemas for the database, and you will create an actual database using a relational database management system. You will populate the database with sample data, write interactive queries and modifications on the database, and develop user-friendly tools for manipulating the database.
Try to pick an application that is relatively substantial, but not too enormous. For example, when expressed in the entity-relationship model, you might want your design to have in the range of four or so entity sets, and a similar number of relationships. Note that this is a ballpark figure only! You should certainly include different kinds of relationships (e.g., many-one, many-many) and different kinds of data (strings, integers, etc.
Step 1. Describe the database application you propose to work with throughout the course. Your description should be brief and relatively informal. If there are any unique or particularly difficult aspects of your proposed application, please point them out. Your description will be graded only on suitability and conciseness.
Step 2 Specify an
entity-relationship diagram for your proposed database. As always, don't forget
to underline key attributes and include arrowheads or letters indicating the
multiplicity (arity) of relationships. We will use a simple methodology to
translate your E/R model to equivalent tables (relations). For each entity set
create a table. Also for each many-to-many relationship create another table. A
many-to-one relationship can be included in the relation that is at the many
side of the relationship. A one-to-one relationship can be included in either
of the related entity sets.
Later when we learn the
translation methodology from E/R model to the relational model and the
normalization theory you can refine your database schema and change it.
Reconsider your relation schema in the light of the theory of normalization and
BCNF. Remind us of your chosen database schema. For each of your current
relations, tell whether its relation schema is in BCNF. If not, then either
redesign your schema so the relation is in BCNF, or give a rationale for
leaving in non-BCNF form (e.g., the amount of redundancy introduced is minimal,
and splitting the relation would cause some reasonable queries to become
multi-relational. Indicate your final choice of design, whether or not you
choose to decompose one or more relations.
However, this may be too late since you have already done a substantial
part your project. If you elect not to change your project please describe in
your project report the changes to be done in light of what you learned in
database design.
Step 3. The user interface of you application should be menu-driven and include a MAIN menu. It should provide the end user with the following choices at the minimum:
Step 3a. Data Update (insertions, deletions, and modifications)
Step 3b. Reports -to retrieve data from your database. The report should take the user to a submenu with options for retrieving information from any relation (by Primary/Secondary Key) or from a join of two or more relations. Develop your own retrieval possibilities depending on the particular application you have chosen. Flexibility of retrieval is desirable.
In addition to these you may come up with other choices that might also include a choice for exiting back to the operating system, WINDOWS.
The "Data Update" should take the user to a submenu with options for
- Updating (an) attribute value(s),
- Deleting a tuple (row),
- Inserting a tuple,
- Etc. in any one of the underlying relations as appropriate.
The "Reports" option of the Main Menu should take the user to a submenu of appropriate report choices that can either be displayed or printed. Try to include a chart or graph in your report derived from the data stored in the database.
Feel free to use the Command Language, Report Generator, Forms Generator, Applications Generator, SQL and any other facilities of the database system (Microsoft Access) in completing your application.
This project has the sole aim of giving you an opportunity to take a real world situation and implement a database application. However simple it may be, model it as a database and develop an implementation for it that might even be actually used profitably. In other words, make it as realistic as possible.
The specifications of the project are too loose on purpose, so that you can exercise a substantial amount of personal innovations and creativeness to the extent that the limited time you have allows it.
Some suggested applications are:
a) Video Cassette Rentals (Video Cassettes, Customers, Borrowing),
b) Library Circulation Systems (Library Materials, Readers, and Borrowing),
c) Stocks Market Brokerage Houses (Stocks, Customers, Portfolios),
d) Football Federation License Records (Football Players, Football clubs, Licenses),
e) Doctors office system (Patients, Treatments, Prescriptions),
f) Car Insurance Application. (Customers, Policies, Claims),
g) Dental office,
h) Restaurant,
i) Tracking web accesses or collecting data on the web,
j) Etc.
Of course, you are welcome to come up with your own application ideas.
WORK SUBMISSION STANDARDS and ACADEMIC INTEGRITY
Students are reminded of the Baruch College guidelines that relate to the
integrity of student behavior regarding submissions and assignments. In
assignments you can talk one another – which is desirable- to learn the
material better. However each student should submit his/her original work. You
are not allowed to see and copy from the solutions to exams and assignments
provided in the prior terms. The penalty for cheating
is immediate failure in this course.
Group submissions are to reflect the original work of all teammates. Students will be required to submit a memo attesting to that fact with all submissions of work. Furthermore, each teammate’s relative contribution in group assignments will be evaluated.
Behavior during exams is expected to conform to Baruch College guidelines. No further warnings will be given prior or during the exams. Incidents of improper behavior (any form of cheating or communications with other students) will result in a course grade of ‘failure’, and referral of the matter to the appropriate Dean of Students.
Depending on our progress in class I may arrange one or two lab sessions on Access to help students having difficulty in learning Ms Access. Tutoring will be available for Ms Access. Hours and location will be announced when the term begins.
Students are expected to spend significant time outside of the classroom learning to use Ms Windows and Ms Access 2000. It is assumed students know how to run multiple programs at the same time, make backups of their work on several floppy disks, cut/copy and paste text and images between applications, and how to work with the accessories that come with Ms Windows such as a Web Browser (Internet Explorer or Netscape), Windows Explorer (file management), Ms Paint, Notepad and WordPad. If you feel you are lacking these skills, please acquire them either on your own or via one of the fundamental courses you can also get help from the course tutor.
All the books, the textbook as well as the books listed as additional references will be available in the reserve section of Newman Library.