ITDA1001-Database Fundamentals

Objective

This assessment item relates to the unit learning outcomes as in the unit descriptors. This checks your understanding about database designing and management techniques.

Details & Problems 

In this assignment, you are required to answer the short questions, create the E R Diagram, normalize tables, develop SQL statements to demonstrate your ability to use Select, Update, Delete, Create, Alter and Drop statements, and show your ability to create Views and Procedures.

Q1) Mention two advantages of using a DBMS in place of traditional file system. 

Q2) What is functional dependency? Explain it with example. 

Q3) The Australian Superstore Company (ASC) is currently using a traditional file-based system for storing their data. The company is growing fast, and they would like to start using a database management system for data management. They have wisely hired you as a database designer (at your usual consulting fee of $1500/day). Your task is to create an E-R diagram for ASC considering the following points. You can also make any necessary assumption and should list it in the document. 

a.ASC has different branches in multiple locations. Each branch is identified by an ID and has address, phone number and managed by one manager.

b.A manager can manage multiple branches and are identified with unique ID. They also have name, address, phone number and salary.

c.ASC has several employees working in different branches who reports to one of the managers and have an ID, name, address, phone number and salary.

d.ASC mainly sells four types of products, namely grocery, household, clothing and toys. Each product can be associated with an ID, name, description and cost.

e.An employee can work only in a single branch while a branch has several employees.

f.Each branch sells the four categories of products mentioned above.

g.ASC has many suppliers for supplying the products. Each supplier is responsible for delivering only a specific product. The suppliers are contracted for a specific period and have varying rates.

h.Each supplier maintains communication with one of the branch managers to negotiate and report about their service while a branch manager manages multiple suppliers. 

Q4) Consider the following order details for ABC company who are sending this order to their supplier for obtaining few products. Give the third normal form based on the details provided below:

Q5) Write SQL statements for following:

a.Retrieve a list of Northwind’s Customers (names) who are in cities where there are no suppliers. 

b7.List all the product names from Tokyo Traders where the product's unit price is less than the average unit price of all products. 

c.List all those cities that have both Northwind’s Supplier and Customers.