Oracle / database assignment help

[embeddoc url="http://qualityassignmenthelp.com/wp-content/uploads/2017/03/ProjectPartA.pdf" height="1500px"]

 

 

Part A: Step 1 (25 marks) Create a logical (relational) data model (e.g. Database or ER diagram). Use J Developer or Visio to create this diagram.

 

Use appropriate naming conventions while naming tables, attributes, relationships, etc. Also use appropriate data-types, data-sizes, etc. in the diagram and ensure to flag PKs, FKs, etc. There should be two business rules for each relationship (association) shown on diagram. State these business rules very clearly under a separate section (in your report). Similarly, state your reasonable assumptions (this helps us better evaluate your design) under a separate section. Examine each table’s (entity"s) attributes for dependencies and apply the rules of normalisation discussed in Chapter 6 of the textbook. You are required to show the detailed normalisation process e.g. how you move from lower normal form (e.g. 1NF) to the next (e.g. 2NF) in the submitted work. Use dependency diagrams for depicting all the relationships among a table’s attributes. Normalise tables up to the highest possible NF.

 

Part A: Step 2 (9 marks) Generate physical model based on the logical model created in the Step 1. To create the required set of tables, relationships, constraints, etc. write SQL scripts using a text editor (e.g. Notepad). Use SQL Developer for initial checking / debugging and running of the code. Keep improving your code until you are happy with the design. Check carefully the DDL code in your script file and make sure that all the attribute definitions (e.g. names, data types, data sizes, domain values including defaults, etc.), table constraints (entity integrity, referential integrity, etc.), etc. are specified correctly

 

Validate your work by ensuring that query transactions (a) - (p) listed towards the end of the case study are supported by your design. Make sure that the physical design is a robust one and meets the requirements of a good design and remember normalisation alone does not produce a good design. In the end, ensure that both logical and physical designs are consistent with each other. Run your final scripts to generate the database (i.e. physical model). Use your Group Oracle a/c to implement the final code.

 

 

Part A: Step 3 (6 marks) Now create appropriate test data to populate the tables you created in Step2. You can use SQL Developer to run a script file that contains SQL INSERT statements or directly enter data. There should be reasonably sufficient number of rows of test data across all the tables but no table should contain less than five rows. Make sure your test data appropriately and sufficiently reflects (this could sometimes mean adding more than 5 rows in some tables) and does not violate any of the constraints declared while creating the database. Set up all the tables and their constraints in the correct order before you load any data into the database.