CMSC 461: Database Management Systems
Final Project
Phase I
CMSC 461
Database Systems
The final project in this class will give you the opportunity to design and implement a moderately large database system in a domain of interest to you.
Rather than specifying one fixed domain/task specification that the entire class must address, you will be able to select a domain/task particularly relevant to their outside interests, such as an investment portfolio database, a medical database, or a small business database.
The purpose of the first phase of the project is for you to specify which domains and tasks you plan to cover (to confirm that they are suitable and sufficiently challenging) and to begin the database design and implementation process. If you have any questions regarding the suitability of your proposed domain, please discuss the possibilities you are considering with me before the Phase I due date.
In general, your final project will constitute an end-to-end system, with an interface for data input (either from the user via GUI forms, or through data-extraction/imports form external data sources), and an interface for data output (both through the generation of reports, complex SQL queries and definitions of views to provide customized perspectives on the database for different users).
Phase I Due date: April 22, 1999
Phase I Requirements:
Submit a typewritten (or computer generated) proposal that addresses each of the items 1 - 8 below. The proposal shall have a cover page that will include your name, SSAN, Course and section, and the date. This proposal should be formatted as eight numbered items with sufficient detail so that your proposal may be understood and evaluated
1. Briefly describe your target domain (e.g. a compact disc club management system)
2. Give a reasonably comprehensive and representative list of the English questions you would like your system to be able to answer. For example: "List all customers who have purchased more than 5 CD’s in at least one of the past 3 years and are not currently more than $50 behind in payments.
3. Design and draw an Entity-Relationship data model that captures your database design. (Can be hand-drawn.)
4. Design and show a relational data model that you plan to use for your system. This may either be:
A standard table representation with the header and one tuple of sample values for each relation. Underline primary keys and indicate referential constraints with arrows between foreign keys and the relation/attribute they reference or
A full relational table specification in the SQL Database Definition Language (DDL). This specification should include both the data type of each attribute, the NOT NULL constraint when appropriate and sample data values for each attribute represented as comments. You should also specify the primary keys (e.g., PRIMARY KEY(ssn)) and referential constraints (e.g., FOREIGN KEY(mgrssn) REFERENCES employee(ssn)). Although this may be slightly more work now, it will save you time later as this full DDL specification will be required in Phase II.
5. Write SQL statements that will implement a representative sample of your target queries, including some of the more interesting or challenging cases.
6. Provide a proposal of how you will load the database with values.
If you plan to extract/import data from on-line sources, briefly describe what are the sources (e.g. personal data, or WWW URL’s) and what are any format conversion issues you expect to encounter.
If you plan to import your data primarily through a form-based interface, briefly describe this interface and the issues involved.
7. Very briefly describe a type of reports you plan to generate or any special user interface issues (e.g., views) that you plan to implement.
8. Ensure that your project includes at least one of each of the following:
9. Your project must include at least five tables and one query must extract data from at least three tables.
Your proposal with be marked as "Approved" or "See me". If you receive "Approved", you can immediately begin Phase II. If you receive "See me", I will talk to about about any concerns I have and you will resubmit your proposal with any necessary changes.
You will have to submit your approved proposal as a part of Phase II documentation.
Phase II Due date: May 12, 1999
Phase II Requirements:
The final phase will include the full implementation of each of the points proposed or described in Phase I, including a full suite of the SQL queries to answer interesting questions in the domain. When you are building the system, put all the SQL and PL/SQL in a file that you will run.
A demonstration of the system will be required, either on-line or through snapshots of sample output. This documentation will include the approved proposal, and a copy of all of the files you used when building your system.