Reference no: EM132317444

Assignment Questions -

1. Describe the purpose of normalizing data.

2. Describe the types of update anomaly that may occur on a relation that has redundant data.

3. Describe the concept of functional dependency.

4. What are the main characteristics of functional dependencies that are used for normalisation?

5. For the following table of data (a) Show the dependencies based on the given data (you can leave out any functional dependencies that are derivable from those you have already obtained); (b) Describe the anomalies that can occur; (c) Draw a dependency diagram, showing all partial and transitive dependencies on the primary key; (d) Perform a 2NF.

6. Read prac8tables.sql and draw a logical data model to illustrate the design. It may be helpful to refer to the sample data used in prac8data.sql.

Practical Questions -

7. Execute the SQL scripts that have been uploaded onto vUWS for this prac session. These are called prac8tables.sql and prac8data.sql. Read each line of these scripts and ensure that you understand their execution.

8. Write an SQL query to show the average customer balance for each area code.

9. Write an SQL query to show all customers and the products they have purchased. The result should be the same as the following:

10. Modify the query above so that the vendor name for the product is also displayed, as follows:

11. Suppose a table Dates is created and filled with data via the following SQL script

CREATE TABLE Dates (d DateTime );

INSERT INTO Dates VALUES ('1990-09-05');

INSERT INTO Dates VALUES ('1999-01-02');

INSERT INTO Dates VALUES ('2001-02-14');

INSERT INTO Dates VALUES ('2014-08-01');

INSERT INTO Dates VALUES ('1999-01-05');

List all the dates in the reverse order.

12. Formatting output in SQL*plus, students are encouraged to experiment with the following aspects of formatting output.

13. Make up for SQL Server Visual Track 4 ­ Queries with Simple Join and Grouping. If you haven't got time to do this part in the previous practical and you are willing to give it a go even though it's optional, you are welcome to go back to complete it.

Additional Exercises -

1. Describe a simple database of your choice or design, along with the table/s representing the data, and illustrate the Insertion Anomaly through the real data or records there. Your database should be different from those already covered in the lectures or practicals.

2.  (optional) For the database created by prac8tables.sql, whose relation diagram (the 1st diagram there) is shown earlier on (in this prac when in the solution display mode), list enough functional dependencies implied by this relation diagram so that all the functional dependencies are either in this list or can be derived there.

3. For database in the previous question along with its inserted records prac8data.sql, do any 2 of the following database queries. For each query, take a screenshot for the query result and the screenshot is to include the SQL script window as well. Before finalising your answers, refresh your database by reloading prac8tables.sql and prac8data.sql, if necessary, so that the results are uniform across the board.

a. List P_DESCRIPT and P_PRICE for a product and the name V_NAME of the vendor who can supply the product. The output should be sorted according to P_DESCRIPT and then the V_NAME, all in the alphabetic order.

b. For the customer Phan Wang, list the names (i.e. P­DESCRIPT) of all the products ever ordered by this customer. Do not repeat the product names in the result.

c. List all the customer order (ORDER_CODE), the fullname of the customer, and the corresponding total cost for each order.

4. For the database in the previous question, list the average cost for the customer orders.

Attachment:- Assignment File.rar