Analyse and comprehend a provided er diagram

Assignment Help Database Management System
Reference no: EM13732484

Objectives:

To analyse and comprehend a provided ER diagram and Database Schema

To implement a database based on the provided ER diagram and Database Schema

To write required SQL statements to query the database

To implement the database system, you are required to

a) provide SQL commands to create the database, its tables, the relationships of the tables

b) Create a text file called YourStudentId-Create.sql (format xxxxxxx-Create.sql) for example 2225991-Create.sql that will provide SQL commands to:

i. create a database called BigM[YourStudentID] (eg BigM30011111)

ii. create all of the required tables for the database including their primary keys, foreign keys and the relationships of tables.

c) Create a text file called YourStudentId-Insert.sql (format xxxxxxx-Insert.sql) for example 2225991-Insert.sql that will provide SQL commands to:

i. insert sufficient data into each table you have created to test the queries (at least 5 to 10 records in each table; some table may require less or more).

ii. You are required to include your full name as one of the employee and/or customer; but you can provide fake details for your address and contact.

d) Create a text file called YourStudentId-Queries.sql (format xxxxxxx-Queries.sql - for example 2225991-Queries.sql) that contains all of the queries to display the following reports:

1. List of names and complete address of all employees sorted by their salary. Also indicate if the employee works fulltime or casual.

2. The date on which the most recent customer order has been made. The customer name and date of order will suffice.

3. List of all the store names and their addresses, sorted in dictionary order of the store name.

4. A list of all customers that have not placed an order yet. Displaying customer number and name will be sufficient.

5. A list containing the name of employees, which work as accountant.

6. A list containing the total quantity on hand for each product (product no and description) regardless of stores.

7. A list showing each product sold (picked) on or before May 20, 2015. Show product number, name and quantity sold, sorted by product number and then quantity sold.

8. A list of CDs (show CD title, release date and price) whose price is less than or equal to the average CD price.

9. Increase each fulltime employee's salary by 7.5% and show the updated salary of all fulltime employees (name and salary).

10. Show all the artist names and CDs in which they appear, sorted by artist first name and then by last name and finally by CD title.

There are a number of supertypes and subtypes (entities) in the ERD (see below). For example, for the supertype EMPLOYEE there are two subtypes FULLTIME and CASUAL.

Also for the supertype PRODUCT there are two subtypes CD and OTHERPRODUCT.

While an employee can work only ‘fulltime' or ‘casual', a product can only be ‘CD' or ‘other product'. Thus, the relationship from a supertype to one of its subtype is one to one and there is an optional participation sign on the subtype side. Also, when an employee works as ‘fulltime', he/she cannot work as ‘casual', and vice versa. Similarly, when a product is a ‘CD', it cannot be in an ‘other product', and vice versa. Therefore, a special design requirement has to be observed as follows:

- An employee working ‘fulltime' in EMPLOYEE table can only be entered into FULLTIME table (i.e., trying to insert it into CASUAL table will show an error),

- An employee working ‘casual' in EMPLOYEE table can only be entered into CASUAL table (i.e., trying to insert it into FULLTIME table will show an error),

- A product designated as ‘CD' in PRODUCT table can only be entered into CD table (i.e., trying to insert it into OTHERPRODUCT will show an error), and

- A product designated as ‘other product' in PRODUCT table can only be entered into OTHERPRODUCT table (i.e., trying to insert into CD table will show an error).

Reference no: EM13732484

Questions Cloud

How much will the annual payments : A Wartburg engineering student graduates with student loans in the amount of $41400 that have a repayment APR of 7.66%. How much will the annual payments be if they intend to pay off the loans in 14 years?
Defined contribution pension plan : The defined benefits pension fund of G-Tech Electronics has a net value of $2.71 billion. The company is switching to a defined contribution pension plan.
What was the cpa during that two week period : Which two week span produced the highest sales total? What was the CPA during that two week period
The sale to actually use school construction : A $2 million School-bond issue being interest at 15 percent pay le annually and maturing in 25 year was sold at a price which a 20 percent annual rate of return to the investors. The brokerage fee for handling the sale was 0.3 percent of the bond iss..
Analyse and comprehend a provided er diagram : Analyse and comprehend a provided ER diagram and Database Schema and mplement a database based on the provided ER diagram and Database Schema
From an economic growth and prosperity level : From an economic growth and prosperity level, we have to look at the role of the financial system. We know that high inflation means higher interest rates, which lead to less purchasing power and more investing (investors like higher interest rates!)..
Largest distillers of premium scotch liquor : Suppose the European Union (EU) is investigating a proposed merger between two of the largest distillers of premium Scotch liquor. Based on some economists’ definition of the relevant market, the two firms proposing to merge enjoyed a combined market..
Most android phones struggle to be profitable : How is Apple able to maintain high profit margins on its phones, while most Android phones struggle to be profitable? Select the answers that apply and give justification for your choices.
Find out accounting and payroll : An accounting firm has just contacted your consulting business for assistance. They want to hire someone to do accounting and payroll.

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd