Schema Design (PART I)

The following describes what is expected in the design part of the assignment.

1 DFD and ER Diagram

Create a context level DFD. You should take your DFD to your tutorial for feedback before starting on the ER.

Create an ER diagram to represent the conceptual schema described by the Universe of Discourse. For cardinality and participation dependencies you may use either standard notation, or the alternate (min,max) notation, but not both. Depending on your design choices, you may need to use the extended ER diagram.

State clearly any assumptions you make regarding your design approach. Please note that you cannot make assumptions to simplify or compromise the completeness of the Universe of Discourse. If there are any points that need clarification, in the specification of the Universe of Discourse as given above, you must first attempt to clarify them with your tutor and/or lecturer.

2 Mapping

Map the ER diagram created in 2.1 to a relational schema. Document the mapping steps. The final schema should be given in the notation: R1 (keyattr, attr1, attr2, ...). In order to identify referential integrity constraints, be sure to either use the same names of the referenced/referencing attributes, or write this separately e.g. R1(attr1) references R2(somekeyattr). You can also use a schema diagram which identifies referential integrity constraints through arrows.

3 Normalization

All relations resulting from the ER-Relational mapping should be normalized (if required) up to the third normal form (3NF). For each relation given in the final schema from 2.2 show the following

1.    Identify non-obvious functional dependencies

2.    Determine if there are any partial/transitive dependencies

3.    If necessary, decompose the relation to make 3NF relations

4.    Clearly state the normal form of the original relation, and of the resulting relation(s).

Tip. It is highly likely that the relations in the schema you come up with at the end of the ER-Relational mapping (see section 2.2) are already in 3NF. However, you still need to write the (non-trivial) FDs between the attributes of each of the relations you designed, and demonstrate that the relation is actually in 3NF by running the 3NF test on it.

Here is an example of what you may document in this part:

R1 (A, B, C, D) FD: AààA, B, C, D No partial or transitive dependencies found, R1 is in 3NF

PART II

The following describe the scope and requirements of the implementation in MySQL:

1 Tables and Constraints

Create a database in MySQL called MOVIE You need to implement the final schema after your normalization in MySQL by creating the tables and constraints. Be sure to use meaningful table names and before final submission remove any unnecessary or temporary tables from your database. Your database should enforce basic constraints, such as:

• - Referential integrity. Multiple referential integrity constraints can be extracted from the specification.
• - Domain. Attribute values are restricted to the allowed data types.
• - Key and Entity integrity constraints.
• - Semantic constraints if any/as given in the Universe of Discourse.

You also need to create visual diagram to represent your database using phpMyAdmin Designer.

2 Sample Data

Populate the database with enough meaningful sample data (at least 10 tuples per table) to allow us to test the functionality offered by your information system1.