database analysis (ERDs), design (LDMs), and implementation (physical design & forward engineering).

Final Project is the culminating experience for you, and will allow you to see database analysis (ERDs), design (LDMs), and implementation (physical design & forward engineering). Once implemented, you will populate your database with data and runs queries to see the results.

Groups and Collaboration

Be sure to work with your group members.

Each team must also have a unique project. If two teams have the same topic or are very similar, I will reduce both project grades by one letter grade.

Part 1: ERD/LDM Modeling (30%)

Your team must design a database for a company, charity, or other organization. My suggestion is to choose a focus team member, and have him or her act as the user. As an example, if someone loves dogs, he or she might act as the owner of a company for boarding dogs.

In this first phase, you should start with ERDs to elicit the user’s requirements. The ERD can of course contain many to many relationships, and need not include FKs, or PFKs.

Important: you may not do a database that reflects the same or similar context as one we have done in class. For mini-project examples, you may use them (with my permission) if your team demonstrates a significant extension of the original problem. If your team does a database that I deem overly similar, you will receive a grade reduction.

The second part of this project is the development of an LDM, based on the ERD. All many to many relationships must be resolved, PKs/FKs/PFKs must be defined, and all non-key entities must be included. All LDMs must include:

• At least one unary relationship
• At least one supertype-subtype relationship
• At least one weak entity
• At least one required (NN) and one optional foreign key
• At least one of the following data types:DECIMALDATE, TIME, or DATETIME

For the DECIMAL field, make sure it is something that makes sense to aggregate (e.g., sum or average). When you have your ERD and LDM completed, I suggest that you set up a meeting with me to review it together. I will provide targeted suggestions so that you can ensure you are on the right track before moving forward.

Part 2: Forward Engineering & Data Entry (20%)

Forward Engineering. Once your LDM is stable (and ideally approved by me!), your next step is to implement it on MySQL using forward engineering. You will need to create a MySQL EER model, with the appropriate identifying and non-identifying relationships.

Data Entry. Before starting to enter data, I encourage you to take the time to review your MySQL EER and confirm all data types, null/not null, relationship cardinalities, naming conventions (all tables are capitalized/all fields are lowercase) are correct. If you find a mistake and have to correct it, you will likely lose all entered data.

The name of your database schema must be studentXXX_Final, where XXX is your assigned account number.

When you enter the data, reminder that you must consider the dependencies when choosing the table entry order. Start with the tables with no foreign keys, etc. Make sure your foreign key values exist as primary keys in another table, or you will get a foreign key constraint error. Each table should contain at least five rows of data. Make sure that one of the optional foreign key values has at least one value of .

Note that your team cannot move on to the next section until you have successfully forward engineered and entered the data. And obviously, teams cannot receive credit for the SQL section without a database and data.

Part 3: SQL Queries (50%)

For the final part of the project, you will construct and implement SQL queries to pull information out of your new database. The queries must include all of the following:

• GROUP BY aggregate query. Query must sum or average one of the DECIMAL fields
• HAVING that breaks down an aggregate query by some categorical factor
• WHERE statement that uses wildcards for pattern matching
• INNER JOIN. Join together at least two tables
• INNER JOIN. Join together at least three tables
• OUTER JOIN. Illustrate the use of an outer join by joining the table with the foreign key. Note that the result should include that row

Note that all queries should of course utilize ORDER BYs (when appropriate) to order the data for easy reading. For example, when featuring employee information, the output should be ordered by last name and first name. In total, your team must have at least five queries.