For every entity, create small tables that include all the attributes of the entity, its datatype, if it is required, the possible default value and possible restrictions and other remarks. Use the name of the entity as the title for the table. Mark the attribute or attributes that serve as the key by, for instance, underlining. Also mark the fields that you are planning to define a secondary index into.
Transform your ER-diagram to relations of the database. Document the transformation as in the exercise 2.
Check every relation in the database and consider whether they meet the following requirements:
In case you find problematic relations, normalize them by dividing the tables. If the changes are marked, update the ER-diagram as well. Explain the changes verbally in the documentation of the assignment.
Write the statements needed for creating the database as in the exercise 3 and on the lectures (SQL92). Do not use unstandardied datatypes or other definitions.
Remember to save the SQL you write since the statements must be included in the assignment. The supervisor must be able to create the database by using your SQL, so pay attention to the order of the statements.
Also write the SQL for creating secondary indexes.
Also write the SQL for deleting relations.
Write SQL for putting in test data. At least five records must be inserted to every table. List the input satements in the correct order according to the referential integrity.
Write the queries for the reports mentioned in the requirements or make up some new ones. Do not use any unstandardized functions.
Write at least seven different reasonable queries.
GROUP BY)