http://yunus.hacettepe.edu.tr/~tonta/courses/fall2005/bby309/bby309-2005-final-project.htm
Hacettepe University Department
of Information Management
BBY 309 Database Management Systems (Fall
2005) Yaşar Tonta
FINAL
PROJECT:
A DATABASE FOR THE DEPARTMENT OF
INFORMATION MANAGEMENT
(Worth 20 points; due 02 January
2006 12:30 p.m.)
The Department of Information
Management has some 400 undergraduate and graduate students. It offers both undergraduate and graduate
courses each semester. There are about
20 instructors. Each instructor teaches about
3 to five courses each semester. The
number of students enrolled in each course ranges somewhere between 3 to 120,
depending on whether the course is undergraduate or graduate. The Department has 5 classrooms and a seminary
room where courses are held.
Consider the following set of requirements
for a database for the Department of Information Management to keep track of:
1) student records; 2) courses offered by the Department; 3) instructors’
names and the courses they teach; 4) student grade reports (transcripts); and
5) rooms where courses are held. The
Department wishes to be able to use this database to answer both regular and ad
hoc questions and produce periodic and ad hoc reports.
1.
The Department gets the
registered student records from the Registrar’s Office. Each student record has the following fields:
student’s name, student number, current address and phone number,
birthdate, sex, and class (first-year, second-year..., master’s,
doctoral).
2.
Each course offered by the
Department has a course name, description, course code number, number of credits,
semester it is offered (fall or spring) and the level (undergraduate or
graduate), along with the instructor’s name teaching the course, the
classroom where the course is held (e.g., classroom), and its day & time.
3.
Each records for instructors
contains instructor’s ID, name, room number, phone number, and email
address.
4.
A grade report (transcript) lists
student’s name, course name and the student’s grade (A, B, C, etc.).
5.
The following information about
each classroom is held in the database: building where the classroom is located
(e.g., Faculty of Literature, Faculty of Science), room location (e.g.,
B81K104), capacity (number of seats), and the availability of Internet
conncetion in the room.
Complete the following tasks:
1. Design an ER
schema for this application and draw a diagram for that schema using 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. Create a database for the ER diagram you
drew in step 1 using MS Access and save it under your directory that you used
for the previous assignment. Pay attention to the primary keys and relations
among tables.
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 Department’s database
that you developed.
4. Create two data entry screens for the table(s) you defined and save it. Do not use the default screen format.
5. Develop three SQL queries similar
to the ones given below and run them against the database:
a)
Which courses are offered by Professor X during the fall semester?
b)
Which student’s received a passing grade (A, B, C) from course Y?
c) Which students are enrolled in courses offered by Professor X?
Queries
you are to create shall show the capabilities of the database you design and shall
be answered by means of using more than one table. Save the results of SQL queries separately. Give query names that describe what the query
does (as in “Courses taught by Professor Tonta)”, and “Students
Who Received Passing Grades from BBY 309”).
6. Create two reports using the report
generator. Again, reports you are to develop should
show the capabilities of the database you designed (e.g., create a report for
each course listing the course information, students who took that course and
their grades). Save them.
7. Evaluate
the merits and deficiencies of the database design and how you would improve it
the next time.
Data entry screens, queries, and
the reports should be saved on your folder.
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 IDEF1X diagram that you developed
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, IDEF1X diagram correctly depicting the proposed
solution (40 points), the database itself
(tables, data entry screens, SQL queries, report generation, etc) (40
points), and the report (20 points).
The database design model shall
clearly identify primary keys and foreign keys as well as referential
constraints. The proposed design shall
include the details and the correct IDEF1X notation (relationship types identified,
cardinalities correctly identified, primary and foreign keys correctly
identified, etc.) (40 points)
Database shall be created using
MS Access. Make sure the database you created
conforms to the proposed design given in 1 above (primary and foreign keys,
attributes, referential integrity constraints should be identified). All tables included in the design shall be created
and filled with data. SQL queries shall
be created and they shall be easy to understand (explained what the query is supposed
to do in the Database Report that you are to create using MS Word). The reports
shall be created. Try to beautify your report by formatting it using the
capabilities of the report generator (eg, titles added, fonts, etc.) (40
points)
Database report shall be written
using MS Word. Do not write a procedural
report (ie, “I created the tables, did this and did that”). The report shall specify the advantages and
disadvantages of the proposed design. What
would you have done differently if you had more time, for example? (20
points)
Please note that,
even though the scenario given above suggests a certain design approach, this
assignment can be completed in different ways: there is no “single
correct design” to set up the database.
It all depends on your reading of the entities and the relationships
among them. Do not try to come up with a
detailed and complicated desi,gn. Remember
the motto: Keep it simple.
Good luck.
Yaşar Tonta
Released: 28 November 2005