MSI 520: Database Design and Application Development
Coursework
Assignments
Assignment given in any week is due at the beginning of the class in the next week. There will be a penalty of 10% per day for late assignments unless there is a very pressing reason for the delay. Please work individually on all assignments. Stop by my office if you have difficulty in understanding the assignment or the course material related to the assignment.Tutorial
Each student will complete tutorials from the tutorials that come with this book.Library Database: library2000.mdb
University Database: univdbchpt3A2000.mdb
Database Final Project
Design a database for a realty company. The database should list all the houses that they have listed for sale. Houses should have a list of attributes which are characteristic of the house. Also have a database of communities in which the houses are located and include a list of community attributes with each house. Create a table for all the real estate agents in the company. Each house should have an associated real estate agent who represents the seller. All the bids on the listed houses should also be maintained in a database. All the completed transactions should be listed in the database.Entity-Relation Model (4pts): For each entity identify the attributes, and relationships. List all possible keys for each entity and select a suitable primary key. Draw an entity relationship model.
Normalization (4pts): Normalize your data to Boyce-Codd Normal Form and describe your normalization.
Database: Create the database in Access and populate the tables with the data.
Queries(10pts): Put enough data to demonstrate queries on the data. Formulate at least 10 queries, including SELECT, CROSSTAB, ACTION, and PARAMETER queries. Run the queries and present the results of running the query. Make sure that you have both single table and multiple table queries. Also show use of aggregate functions in the queries. Exercise the QBE option as well as the SQL option for entering the queries.
Forms/Reports(4pts): Create a form with control buttons to execute a query, to open a form, save a form, print a form and run a report. Generate one or two reports from the database.
Project Report(4pts): As a part of your project you should prepare a project report which describes the design of your database system and the assumptions which you have made. Please include the E-R diagram, schema (table names, attribute names, primary keys) and the results of the queries as well as the SQL syntax used for running them.
Project Submittal(4pts): Please provide a hard copy of the project report (including all the elements of the project report) and the database on a floppy. Please do not email it to me unless a zipped copy of the database does not fit on the floppy.
Project Teams: Not more than two people should work on a project. In case of two people working on a project a report from each member highlighting his/her contribution to the project is required.