http://yunus.hacettepe.edu.tr/~tonta/courses/spring2005/dok322/d322-2005-final-project.htm/
Hacettepe University
Department of Information Management
DOK 322 Database Management Systems (Spring
2005) Yaşar Tonta
FINAL
PROJECT:
A UNIVERSITY DATABASE
(Worth 20 points; due 16 May 2005
09:00 a.m.)
Consider the following set of
requirements for a university database that is used to keep track of students’
transcripts:
a)
The university keeps track of each student’s name, student number,
citizenship number, current address and phone, permanent address and phone,
birthdate, sex, class (preparatory class, first-year, second-year..., master’s,
doctoral), department, and degree program (BA, MA, ... PhD). Both student number and citizenship number
have unique values for each student.
b)
Each department is described by a name, department code, office number,
office phone, and faculty. Both name and
code have unique values for each department.
c)
Each course has a course name, description, code number, number of
semester hours, level, and offering department.
The value of code number is unique for each course.
d)
Each section has an instructor, semester, year, course, and section
number. The section number distinguishes
different sections of the same course that are taught during the same
semester/year; its value are 1, 2, 3, ..., up to the number of sections taught
during each semester.
e)
A grade report has a student, section and grade.
Complete the following tasks:
1. Design an ER schema for this application and draw
an ER diagram for that schema (use IDEF1X notation). Specify attributes (including primary keys) of
each entity, types of relationships (1:1, 1:N, M:N), and structural constraints
on each relationship type. Note any
unspecified requirements, and make appropriate assumptions to make the
specification complete.
2. Crate
a database for the ER diagram you drew in step 1 using MS Access and save it
under your directory that you used for Cookie and Diveshop assignments.
3. Enter data in to the tables that you created. The objective of this project is
not to enter data. So, between 10 and 20 records
should be more than enough. It is
important that the data you enter will show the capabilities of the university database
that you developed.
4. Create a data entry screen for the table(s) you defined and save it. Do not use the default screen format.
5. Develop three SQL queries and run
them against the database. Queries should be answered by
using the table(s) that you developed.
Save the results of SQL queries separately (as in
“Myquery1”, “Myquery2”, and “Myquery3”.
6. Create a report using the report
generator. Report should get data from the table(s) you
developed (in addition to the existing ones). Save it.
7. Evaluate
the merits and deficiencies of the database design and how you would improve it
the next time.
Data entry screen, queries, and
the report should be saved on
your database.
Problems you identified and the solution you proposed, along with your
evaluation of the merits and deficiencies of your design, should be written as
a Word document and saved under your folder. The E-R diagram that you develop
should also be saved as a file under your folder. (Use a drawing program to do
this.)
GRADING: Grading will be based on the
quality of the proposed solution, ER diagram correctly depicting the proposed
solution, data entry screen(s), SQL queries, report generation, etc.
!!IMPORTANT!!
Final copy of your project must include;
* your database (that is an .mdb file with tables, forms, appropriate queries and reports in it),
* an e-r diagram of your database (ppt file),
* and your report as a text (.doc file).
Please add these files into a single "zip" file. The zip file must be named with your student number (like, "20041234.zip").
This zip file must be sent to dok322@gmail.com before 16 May 2005, with your student number as the "subject" of the mail. The evaluation of your projects will be made according to the files which you will post to the above address. In order to be sure, you may also send a copy of your project to your own e-mail address and keep it in your inbox until the end of evaluation period (June, 2005).
Good luck.
Yaşar Tonta
Released: !8 April 2005