Assgnment: Personal Collection Database Application
Mapping from E/R Model to Relational Design
Project Description
The course project will be spread over five phases over the rest of the semester.
The weight of this project component is 10% of the course grade. Please submit one "pdf" document that includes the following:
Entity--Relationship to Relational Schema:
(a) A copy of your updated E/R Data Model from Phase#1 of your PC--DBA.
Use MySQL Workbench or a similar software system to generate your updated diagram. Hand--drawn diagrams will not be accepted at this point. The course material on Canvas includes several tutorial videos about the MySQL Workbench.
(b) Use the methods for transforming an E/R diagram to Database Relations described in the text or the supplementary lecture notes and videos posted on the course's Canvas website, to produce a set of relations from your E/R Data Model. Specify your relational schema using the notation described in these resources, and be sure to indicate key attributes.
(c) For each Database Relation, add your comments to respond to the following question:
Are there any flaws in the relational database schema you get from part (b) above?
Normalization:
1. Are there any opportunities to combine relations without introducing redundancy? If so, indicate which, and if not, tell that there are none.
2. Are there any examples relation schemas that violate the Third Normal Form (3NF) or Boyce-- Codd Normal Form (BCNF)? If so, do you want to decompose them? Note that in the Design phase, you may knowingly decide to violate 3NF, BCNF or 4NF when you prefer to avoid decomposition of a big relation R, because you anticipate frequent queries on R and if R is decomposed into R1 and R2, then that will lead to frequent expensive joins of R1 and R2.
3. For each opportunity to combine or decompose relations, decide whether or not to do so, and explain your reasoning briefly (e.g., tell what queries you expect will be typical for your database, and tell how the design you pick facilitates them).
4. Is there anything you still don't like about the schema (e.g., attribute names, relation structure, duplicated information, etc.)? If so, modify the relational schema to something you prefer.
5. You will be working with this schema quite a bit, so it's worth spending some time to make sure you're happy with it.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.