Implement a database based on the provided er diagram

Assignment Help Database Management System
Reference no: EM13747958 , Length: word count:1000

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

Implementation of 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.

Problem (note: edited from the above source. Please take it as an example, not an exact implementation for the assignment's supertype-subtype relationships):

I need to create the following database:

Vehicle can be semi-trucks and cars. For semi-trucks I don't need extra subtypes, while for Car I need to have only 3 subtypes (Van, Suv and Sedan) and also for Sedan I need the four subtypes (Gas, Diesel, Hybrid and Electric). For SELECTs I will use JOINs (normalized database) but I need to find an easy way to make correct INSERTs.

- Vehicle table stores common information

- Semi-truck stores specific information for semi-trucks

- Car tables has specific fields for cars and a car_type field which is linked to the three subtypes

- Van, Suv and Sedan (and other types if I would need them) should be in one table CAR_TYPE

- However, for Sedan type I need to have additional subtypes which maybe should be contained in another table. These subtypes are not needed for Suvs and Vans (in real life suv, vans can have the same subtypes as sedans but not in my case).

So far, my first approach is to have the following tables:

- Vehicle: veh_id, veh_type(Semi, car), ..., other_fields
- Vehicle_semis: veh_id, ..., other_semis_fields
- Vehicle_car: veh_id, car_type(Van, Suv, Sedan), other_car_specific_fields
- Car_type: car_type_id, type
- Sedan_type: sedan_type_id, type

Reference no: EM13747958

Create a entity-relationship diagram

Create a Microsoft Visio entity-relationship diagram (ERD), based on the relations in the Entities and Attributes for Fleet Truck Maintenance file and any additional informa

Describe discretionary access control

Database security expert to train the database administration team.At the first training session,you ask the trainees to perform the following tasks using Microsoft SQL Serv

Implement the entity relationship model

Implement the entity relationship model provided in the sample solution of Assignment - Map the ERD into a set of relations in at least Third Normal Form (3NF). You must ensu

Construct a b+ tree

Construct a B+ tree for the following set of key value (2,3,5,7,11,19,23,29,31) in ascending order where the pointers that will fit in one node is as follows:A. four B. six

Data mining

DATA MINING-Business and Management Scenario assignment-Data Warehouse Reports. This is Part Three of the three-part assignment. For this week, you will complete the following

Vulnerability assessment matrix

Create a table of analysis based on the attributes below. Use RAND as a resource for referring to your readings. Name the attributes in one column (behavioral sensitivity).

Relational databases are more efficient and effective

Describe why relational databases are more efficient AND effective for data management in organizations. How does the task of data normalization help to achieve these goals?

Problem related pseudocode for a game application

Create the pseudocode for a game application that contains an array of five (5) multiple-choice questions related to your favorite hobby. Each question contains four answer

Reviews

Write a Review

 
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