Milestones

Milestones are designed to give you simulated experience of building a database backend and website frontend for a client. At milestones 3 and 6, your team must schedule a meeting with the instructor for interactive grading and feedback. The sign-up link will be in Canvas.

Milestone 1: Project proposal due on Sep. 3.
Find a partner. The proposal should include

1. Project name
2. Team member's names
3. The client introduction
   You may find your own client, or make up one. For example, if your client is a non-profit organization providing medical care for less privileged kids, then copy their mission statment here.
4. Requirement analysis
   Answer the following questions.
   1. What data will be stored in your database?
      
   2. How much data will be stored in your database? For the full credit, your database must have at least 10,000 items of your choice.
   3. How do you plan to obtain the data? If you have a real client, please submit a support letter from the client that promises to supply the data by September 30th. If you made up a client, explain how you will either generate or use publicly available data.
   4. How will clients use this database? Usage scenario is a good idea. For example, for the non-profit described above, imagine a database of patients so that staff can see the medical history of the patients and notify them regularly for preventive care and regular checkup. Draw a diagram explaining information flow if possible.
5. Benefit
   Explain why using database is a good idea for this client.

Milestone 2: Conceptual Design (ER diagram) due on Sep. 9
Submit an ER diagram of the database for your client. If you have a real client, share the ER diagram to your client and cc the instructor.

Milestone 3: Logical Design (Relational Schema) due on Sep. 23
Convert the conceptual schema from Milestone 2 to relational schema.

• You should indicate the primary keys, foreign keys, and other integrity constraints. The primary keys should be underlined in the schema list. Ideally, at least two of your foreign key constraints should have an “on delete” event (set null or cascade). Also, there should be at least 5 check constraints.
• Explain what attributes in each table you will use to build an index, and which type. Provide justification of your index choice with rough estimate of cost.

Milestone 4: Table Creation due on Oct. 7
You then need to create the tables in Oracle or mySQL. Include the SQL DDL statements in your milestone report. Name the script file used to create tables and specify contraints as create_YourProjectName.sql in your submission. Also include SQL DML statements to populate your database with at least 100 rows (10,000 for Milestone 5) and name your script file as insert_YourProjectName.sql.

• Remember to specify primary and foreign keys during table creation.
• Remember to specify all interity constraints during table creation.

Milestone 5: SQL statements due on Nov. 4
Describe what clients can do using the website, i.e. functionalities, and write SQL statements that will achieve all the functionalities. Submit README file that explains how your SQL statements satisfy your Requirement Analysis in Milestone 1.

• Clearly define the functional dependencies of all attributes, and illustrate them correctly using the format given in the lecture notes.
• Include SQL DML statements to populate your database with at least 10,000 rows.
• Verify that all relations are in third normal form. For relations already in 3NF – explain briefly how you confirmed it. Normalize relations that are not in 3NF, and explain your steps.
• If you decide to change the normal form for any table, please explain why you changed it and what anomalies you would encounter.

Milestone 6: Web client due on Nov. 25
Develop a web page that can access your database. You may run Oracle and web server in your own machine to simplify the DNS problem. Your web page should be able to provide all functionalities specified in Requirement Analysis in Milestone 1. Submit README file that explains how your website front-end and database backend together satisfy your Requirement Analysis, and the source code of the website. If your SQL DDL statements have changed, submit the new script as well (create_YourProjectName.sql). During the demo, you will need to demonstrate how multiple web clients can access and modify the database content at the same time.