The Gill Art Gallery - Entity Relationship Diagram (ERD) - Computer Science Assignment Help

Assignment Task:

Question 1: 

We provide you with an Oracle sample database which is based on a global fictitious company that sells computer hardware including storage, motherboard, RAM, video card, and CPU. 

The company maintains the product information such as name, description standard cost, list price, and product line. It also tracks the inventory information for all products including warehouses where products are available. Because the company operates globally, it has warehouses in various locations around the world. 

The company records all customer information including name, address, and website. Each customer has at least one contact person with detailed information including name, email, and phone. The company also places a credit limit on each customer to limit the amount that customer can owe. 

Whenever a customer issues a purchase order, a sales order is created in the database with the pending status. When the company ships the order, the order status becomes shipped. In case the customer cancels an order, the order status becomes canceled. 

In addition to the sales information, the employee data is recorded with some basic information such as name, email, phone, job title, manager, and hire date. 

Task 1.1: [2 Marks] Write the SQL query to list the region names and the number of countries within the regions in the above database. 

Task 1.2: [2 Marks] Write the SQL query to find all customers who have made orders before 2017. List must include the customer ID, customer name, and ordered by their ID values in descending. 

Task 1.3: [2 Marks] Write the SQL query to list all customers who have the sequential letters ‘co’ in the customer name. List must include the customers’ ID, names and ordered by their names in ascending. 

Task 1.4: [2 Marks] Write the SQL query to list all products’ ID, Name and price where the products haven’t been purchased by any customer in the database. The list must be ordered by the product price. 

Task 1.5: [2 Marks] Write the SQL query to list all the warehouses and their total sales. Here, given a product, the total sale of the product is calculated by the sold quantity of the product and its unit price. The list must be ordered by the total sales in the descending.

Task 1.6: [2 Marks] Write the SQL query to list the employees and the quantity of orders that they proceeded in the database. The output list must include employee ID, name, and the quantity of orders. The list must be sorted by the quantity of orders in the descending order. 

Question 2: [10 Marks] 

The Gill Art Gallery wishes to maintain data on their customers, artists and paintings. They may have several paintings by each artist in the gallery at one time. Paintings can be bought and sold several times. In other words, the gallery may sell a painting, then buy it back at a later date and sell it to another customer. 

Here is an example record for a customer with his history purchases. 

Gallery Customer History Form 

Customer Name 

Jackson, Elizabeth Phone (206) 284-6783 123 – 4th Avenue Fonthill, ON L3J 4S4 

Purchases Made 

Artist Title Purchase Date Sales Price 

03 - Carol Channing Laugh with Teeth 09/17/2000 7000.00 15 - Dennis Frings South toward Emerald Sea 05/11/2000 1800.00 03 - Carol Channing At the Movies 02/14/2002 5550.00 15 - Dennis Frings South toward Emerald Sea 07/15/2003 2200.00 

Task 2.1: [2 Marks] Draw the dependency diagram of the table, and normalize the table to ensure all generated tables are in 3NF. Present all tables generated from the normalization. You have to present the results step by step from 1NF to 3NF. 

Task 2.2: [2 Marks] Write a set of SQL queries to implement the database schema. You can follow the template in Question 1 – schema.sql or schemaXAMPP.sql. To create each table, it must declare the primary keys, foreign keys, constrains and ON Delete Cascade or ON Update Cascade. 

Task 2.3: [2 Marks] Write a set of SQL queries to add data into the database implemented in Task 2.2. You can follow the template in Question 1 – data.sql or dataXAMPP.sql. The database must include at least five customers, six artists, 10 paintings, and 15 transactions on purchasing. 

Task 2.4: [2 Marks] Write a SQL query to list every customer and their purchased paintings. The list must be sorted by customer name first and painting title second. 

Task 2.5: [2 Marks] Write a SQL query to list the TOP-Three customers whose expenditure are the top-3 most in the database. 

Question 3: [8 Marks] 

Application Description: Suppose there is one bank company – Commonwealth that requires to develop a relational database. The daily duty is to serve their customers managing their saving bank accounts, e.g., withdraw, deposit, etc. Additionally, the company Commonwealth also makes business for the home loan marketing, e.g., a customer may have a home loan account. To set up a home loan account for a customer, there are several information to be collected and recorded:

(1) the customer needs to specify a property address;

(2) a bank staff needs to be assigned to process the home loan application;

(3) the property value should be assessed by using the average of the sold price of the properties located in the same suburb. Note: suppose there are sold properties in every suburbs;

(4) each customer has their own home loan limit that is calculated by his/her annual salary amount multiplied by 7. Note: In general, the bank company used 7 years’ salary to estimate, e.g., if the customer Kevin has annual income $60,000, then his home loan limit cannot exceed $420,000. For the customers having multiple home loans, their total home load amount cannot exceed the customers’ home loan limit;

(5) a customer may have more than one home loan because multiple properties can be bought by one customer. Last but not least, your design should reflect the practical requirement as much as possible, i.e., meeting the maximum business rules. For example, some customers may have home loan accounts, but some ones may only have saving accounts. For a customer who need to set up a home loan account, it must create a saving account first. The average sold price for a suburb should be calculated on demand, not saved directly in the database. 

Task 3.1: [3 Marks] Draw the Entity Relationship Diagram (ERD) of the database designed for the above application scenario. In the ERD, you need to specify the main components including Entities, Attributes, Relationships, Primary Keys, and Constraints. 

Task 3.2: [3 Marks] Write a set of SQL queries to implement the database schema. You can follow the template in Question 1 – schema.sql or schemaXAMPP.sql. To create each table, it must declare the primary keys, foreign keys, constrains and ON Delete Cascade or ON Update Cascade. 

Task 3.3: [2 Marks] Write a set of SQL queries to add data into the database implemented in Task 3.2. You can follow the template in Question 1 – data.sql or dataXAMPP.sql. The database must include at least six customers, five suburbs, 10 sold properties, and home loan records. 

This Computer Science Assignment has been solved by our Computer Science Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.