Final Milestone
Total mark: 10 ( out of 20 of Term project mark)
The goal is to model a complete database of at least 6 entities for the Kickstarter project you have selected
Deliverable
In a PDF file ( LastName1_lastName2_ FinalTermProject.pdf)
Please post the title of your selected project and the URL:
First Name 1 LastName 1
First Name 2 LastName 2
Title:
URL: ( of the project on kickstarter):
A) ERD of your design (2 marks)
Marks will be deducted for :
Not identifying modalities (optionality) in ERDs (if your workbench, phpMyAdmin does not support)
Not denoting weak entities ( you can manually place the “+” on crow foot
Not displaying relationships descriptors in your ERDs
B) Questions ( 5 marks)
For each question, post the question, then please post the query answer in text ( so that I can copy and paste ) and the screenshot of resulting table if applicable ( up to first 5 rows)
• List a 1:1 relationship and the two entities involved in it
• List an M:M relationship and the two entities and the weak entity involved in it and the identifier of the weak entity ( the composite key)
• List at least one recursive relationship ( the SQL statement that defines the table in text + the screenshot of ERD)
• Explain how you made sure 2NF was not violated in your DB. None of your tables can violate 2NF ( explain by example, e.g. we broke down this table into this and this … )
• For user credentials, make sure the length of password is at least 6 character long. Provide An example of the check constraints in your SQL script that enforces such constraint
• Your data model must account for all the exceptions. List at least an exception and your solution to it
• Provide an example of running a query on a M:M relationship of your model
• Provide an example of running a query on a 1:1 relationship of your model
• Provide an example of running a query on a recursive relationship of your model
C) 12 queries (3 marks)
Write 12 queries on your DB to utilize all you have learned in this class by the end of the due date of this project (SELECT, UNIQUE, PRIMARY KEY, JOIN, IN, subqueries, EXISTS, HAVING,…)
– Please insert at least three entries in each of your tables
– You may need more to make sure your design is complete and flowless
– You need to have enough data entered in your DB so that all of your queries have to return some results
D) SQL dump (schema + data SQl statements )
in the same pdf file also include all the statements for creation of your tables, constraints , check etc followed by the insert statements ( all in text so that I can try them at my end) ( you can add some the SQL statements manually as well, in case they dont exists in the SQL dump generated by the phpMyAmdin )
Here is another tip on how make a backup of your DB into a file: https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-management.html (Links to an external site.)
Note: -3 marks will be deducted part D is not included in your report
%25 marks will be deducted for each day late submission ( after two days late, you will get 0)