Database Implementation Queries | Database Management System

Due Date: Database Implementation Queries
Friday Week 11 (See Course Description for further date and time).
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
Project Specification
The management team of BigM now require a partial implementation of the design made
in Assignment 1. In order to keep consistency between the assignments, database
specification containing the ER diagram and the schema is provided in this document. You
should create your database according to this documentation. Please make sure that your
implementation is consistent with this design. This means that your table names (upper
case), field names (mixed case, no spaces) and data types have to be according to the
specifications provided in this document. The implementation phase includes writing SQL
statements to create a database and its tables, populating the tables with data, writing a
number of queries to create reports that can be used by the management team. Your
database should contain sufficient data in each table (at least, 5 to 10 records in each
table; some may require less or more) to demonstrate that your queries work.

About the Book

Database Implementation Queries | Database Management System

Database Implementation QueriesDatabase Implementation Queries 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 OTHER PRODUCT. While an employee can work only ‘full time’ 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 ‘full time’, 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.

Database Implementation Queries | Database Management System

  1. requirement has to be observed as follows:
    ? An employee working ‘full time’ in EMPLOYEE table can only be entered into
    FULL TIME 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 FULL TIME 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 OTHER PRODUCT will show an error), and
    ? A product designated as ‘other product’ in PRODUCT table can only be entered
    into OTHER PRODUCT table (i.e., trying to insert into CD table will show an error).
Details
Book Club Resources
Disclosure of Material Connection: Some of the links in the page above are "affiliate links." This means if you click on the link and purchase the item, I will receive an affiliate commission. I am disclosing this in accordance with the Federal Trade Commission's 16 CFR, Part 255: "Guides Concerning the Use of Endorsements and Testimonials in Advertising."