enable the storage of all the information

Question 1

Based on the above case study, prepare a complete set of tables that would be needed to support NATASCO to develop system that would enable the storage of all the information.

Identify all the important attributes for all the tables that you suggest.  Provide a suitable data type for each of attributes to reflect the usability.

Develop a normalized database for all the tables and you should show an example of each of the 1st, 2nd and 3rd normalization process involved for your tables.

Question 2

(a) For each of the tables created in question 1, identify all the primary keys, foreign keys and also any composite keys that would be used to maintenance the database.

(b) Write the SQL commands to be used to create each of the tables that you would included in the creation of the database for NATASCO.  Assume you own constraints and data verifications.

Question 3

Analyse the case study and identify the entire possible user requirement for NATSCO’s daily and monthly operations as described above.  List them down as different relationship with possible type of cardinality.

Draw an estimated complete ER diagram that would be able to represent NATSCO’s complete database.

Question 4

(a)  Insert some of the appropriate data for each of the tables that you had created in the database.  Use the SQL commands to insert 5 different tuples for each of the tables.

(b) Create your own queries (10) that would be used by the NATASCO’s management team to help them in their daily
activities of using the database.  Use the SQL commands to write your queries and provide the answer from you sample data above