SQL-technical report-access to sql developer

1. What are some of the major issues with the current file based storage model?

2. Are there data anomalies with the current solution? Can you provide specific examples of              INSERT/UPDATE/DELETE anomaly?

3. If you design your database strictly based on the current model (meaning one table to store all information), would it be considered as normalized? Would it be in 1NF, 2NF, and 3NF?

4. If not, describe and illustrate the process of normalizing it to 3NF. Identify functional dependencies and create dependency diagram before each conversion.

5. Show the final table structure after normalization and make sure it is in 3NF.

6. Identify entities and attributes. List entity name and attribute names for each entity.

7. Identify relationships among entities. List relationship type in terms of cardinality and specify business rules (e.g. 1:M between Library and Branch: a library has one or many branches; a branch belongs to one and only one library).

8. For each entity, identify primary key and foreign key if applicable. If you use surrogate key for PK, also specify business unique key (natural key). For FK, also specify parent entity and the matching attribute in parent entity.

9. Create ERD using crow’s foot notation using ER Assistant or Visio. The ERD should incorporate all items mentioned in 2.1, 2.2, and 2.3 (NOTE: 2.1, 2.2, and 2.3 still need to be answered separately in narrative format).

10. Write SQL DDL statements to create tables in Oracle to implement all entities, attributes with the correct data type and data size, primary keys, foreign keys, and constraints such as NOT NULL.

11. Create indexes on all primary key, foreign key, and business key columns.

12. Write SQL DML statements to insert all data shown in the sample spreadsheet into the new database.

13. Write SQL queries against each table so that to display number of records in each table.

14. Write a minimum of 10 queries to query the database.

15. Write SQL statement to create a database view so that a report can be generated by querying the view to create the same set of data as shown in the original spreadsheet. Ideally the view is created by querying base tables directly. However, it is acceptable if it is based on other views but with only one nested level (the final view is against other intermediate level view(s), and each intermediate level view is a query against base tables directly).