Draw an entity-relationship diagram

Assignment Help PL-SQL Programming
Reference no: EM13113913

Part A - Entity-Relationship Diagram (ERD)

Draw an Entity-Relationship Diagram (ERD) for the data storage requirements of a business described in the case study below. Notes:

  • It is recommended that you follow the modelling standard used in the text book and lecture slides.
  • Use only attributes that uniquely identify instances of an entity that are mentioned in the case study. Additional attributes to uniquely identify entity instances should not be introduced.
  • Some information in the case study is ambiguous, vague, and/or missing. This is intentional to encourage students to think, explore, and search for answers. There are two options:

1. Seek clarification about the case study by posting a message on the course forums for this assignment (after searching to make sure the same topic / question hasn't already been discussed). Any clarification received should be clearly documented in your assignment submission.

2. Make reasonable assumptions about missing or ambiguous information in the case study.

Marks may be lost for unreasonable assumptions. The course coordinator will determine if an assumption is reasonable.

The Case Study

ABZ Construction Company has branches (sites) all over Australia and owns several office buildings in all major cities. It wishes to install solar panels on their building roofs and manage these efficiently, as it can provide cost savings. As a result, they hired a consultant to design a database that will store all of the required data. The consultant has been informed that they wish to record details relating to sites, buildings in each site, installers, quotes provided by the installers for specific solar panels and the electricity generated through the solar panels on each building roof.

The data storage requirements are described below.

Each site is identified with a unique site Id, and has a street address, suburb, state, postcode, main contact phone number and a contact person for that site. As each site may have more than one building it is necessary to record Building number, roof availability, average shading factor (indicates the average hours of sunlight per day with a factor between 0.0 and 1.0) and the GPS coordinates (longitude and latitude). For simplicity reasons, calculation for shading factor is not provided here.

Each solar panel has a make, model, size (both width and length in mm), an output capacity (in watts), a recommended retail price (RRP) in Australian dollars, Efficiency percentage (between 0.0 and 100.0) and country of manufacture. Each combination of make and model for a solar panel must be unique. Each installer has a company name, contact person name, phone, fax, mobile number and email address. The date the installer became a solar certified installer and the installer's unique Australian Business Number (ABN) also need to be recorded.

When requested installers provide quotes for installing solar panels on these buildings. Each quote includes Quote reference number, issued date and cost breakdown (solar panel unit cost, number of solar panels, wiring costs, metering costs, labour costs and other costs).

Each building uses electricity (measure in KWh), and this usage is recorded at the end of every billing period (usually the 1 have been installed, a building will generate electricity (measured in KWh) and this will offset the building's usage at the end of each billing period. If the building is producing more energy than it uses, the excess energy will be sold to the grid at a price still to be determined. ABZ want to keep track of the energy produced and the energy used, and the net income or cost, for each building at the end of each billing period.

An installer may quote on the work at one or more locations, or they may decide not to quote at all, for example, the project may be too large for them or they may have too much work already, and so on. If an installer does quote on the work, then they will provide a quote with a reference number that is unique for that installer, and each quote will be specifically for a building on a particular site.

Payments, quote / product evaluation and servicing are outside the scope for this case study.

Day of every month, but this could vary). After the solar panels

Part B - Table design

"ABC Alloys" assigns mentors to their employees as part of their training program and records training cost. It has recorded data relating to employees, Department, Mentors (FYI- Each Department has a Mentor) assigned and the training cost in one table Employee_training.

Below is a small sample of some of this data:

Employee_Training

EmployeeID

EmpName

ContactNo

Position

Department

Mentor

Training Cost

E123

Gary Howard

9864

Senior Manager

HR

George Wellington

 $       5,000.00

E124

Barry Abott

9784

Assistant Manager

Accounting

Ross Trotter

 $       5,000.00

E125

Julie Leonard

2548

Manager

Marketing

Don Anderson

 $       5,000.00

E126

Mathew Foster

6547

Supervisor

Production

Barry Simpson

 $       2,500.00

E128

Richie

8795

Supervisor

HR

George Wellington

$       2,500.00

E127

Richard Drew

5896

Administrator

Finance

Nick Lee

 $       3,000.00

Unfortunately, some anomalies arose while using this design.

Questions

Support your answers to questions below with any assumptions that help to clarify your understanding of data in the Employee_Training table.

1. Identify one anomaly that might arise from using the above table.
2. Identify all functional dependencies in the above mentioned table.
3. Explain why this table does not satisfy BCNF.
4. Convert this table into a set of BCNF tables that can hold the sample data shown in the above table.

Note: Document the tables in your design using the notation below.

Customer(CID, Name, Street, Suburb, State, PostCode, DiscountPercent, Email) SalesOrder(SOID, CID, Number, FullPrice, Discount, FinalPrice, TotalPaid, Status)

foreign key (CID) references Customer(CID)

Part C - Data Model Transformation

Implement the ER model you developed in Part A as a relational database design.

1. Transform your ER model into a relational database design.

Note: Document the tables in your design using the notation shown in the example below.

Customer(CID, Name, Street, Suburb, State, PostCode, DiscountPercent, Email)

SalesOrder(SOID, CID, Number, FullPrice, Discount, FinalPrice, TotalPaid, Status)

foreign key (CID) references Customer(CID)

2. Implement sufficient tables to support the requirements below. You do not need to implement (create) the entire database. You just need to implement enough of the database to demonstrate (and document) the following:

A. use of data types: provide examples of four different data types used in your design

B. primary key constraints: provide two examples

C. foreign key constraints: provide two examples

D. a unique constraint: provide one example

E. a cascading delete: provide one example

F. column constraints (a constraint that affects only one column): provide two examples

G. a table constraint (a constraint that affects more than one column): provide one example

H. default values: provide two examples

Note: You need to include brief documentation (in a text file or word document or in your script file) that documents and highlights where the above items are demonstrated in your database. e.g.

1. A Microsoft Word document containing:

  • your Entity Relationship Diagram (ERD) for Part A
  • your reasonable assumptions for Part A (if you have any)
  • documentation for points of clarification for Part A (if you have any)
  • your answers to questions in Part B
  • your relational database design and implementation for Part C
  • documentation describing the required features of your database implementation for


Part C.

2. Microsoft Access file containing:

  • The tables and features you have implemented to support the requirements for Part C.

Reference no: EM13113913

Questions Cloud

Illustrate what will be impact on individuals who abuse : If Gallo Winery decides to abandon the Thunderbird and Night Train brands, illustrate what will be the impact on those individuals who abuse the dessert wine products?
Testing hypothesis of annual income of teachers : Test the hypothesis that the annual income of teachers in areas of more than 500,000 is significantly more than those in areas of less than 100,000. Use the 5% level of risk.
Describe how environmental change : Describe how environmental change and natural selection combine to shape a population's range of traits and explain how these patterns influence the direction of evolution.
Distinguish these plants from the stones they mimic : Lithops, also called Stoneplants, are a type of plant that resembles little stones. These plants have the ability to blend in with their surrounding, which affords them protection from predators. If you were in an area containing Lithops.
Draw an entity-relationship diagram : Draw an Entity-Relationship Diagram (ERD) for the data storage requirements of a business described in the case study and Data Model Transformation.
Hypothesis testing-compute statistical test : A sample of 40 observations is selected from one somewhat normal population. The sample mean is 102 and the sample standard deviation is 5.
Discuss performance appraisal and evaluation consideration : Explain employee training and develop practices. Discuss the performance appraisal and evaluation consideration?
Find test value for hypothesis : This semester a sample of 55 students revealed an average cost of books of $415 per student. The Dean of Students believes that the costs are greater this semester. What is the test value for this hypothesis?
What is the half life of radioactive iodine : the half life of a radiative carbon is 5700years, if the radioactive material left in the storage has activity of 5ci, what will be the activity after 3000years?

Reviews

Write a Review

 

PL-SQL Programming Questions & Answers

  Query that uses cube operator to return lineitemsum

Write summary query which uses CUBE operator to return LineItemSum (which is the sum of InvoiceLineItemAmount) group by Account(an alias for AccountDesciption).

  Write pl-sql block that displays last name

Write PL/SQL block which displays last name and salary of following people. Each of these can be done separately as PL/SQL, first to test and get output lines.

  Explaining sql insert statement to insert new row in cds

Write down a SQL insert statement to insert new row in "CDS" table.

  Write select statement to return those rows with due balance

Write SELECT statement which returns those rows with the balance due greater than 1000. Sort result set by InvoiceTotal, with largest invoice first.

  Writing and execuinge sql command

The table name will be METALS and will be used to store description of various metals. Following offers some additional direction on how table must be created.

  Write sql statement to create stored procedure

Write SQL statement which creates stored procedure with one int input parameter. The stored procedure selects the supplierId field and the total of all Count field values for each group of supplierId's from the Part table.

  Run the lab_03_01.sql script

Run the lab_03_01.sql script in the attached file to create the SAL_HISTORY table. Display the structure of the SAL_HISTORY table.

  Write a perl subroutine for temperature conversion

Write a perl subroutine for temperature conversion named 'convert_temp'. It should be able to handle both Fahrenheit to Celsius conversions as well as Celsius to Fahrenheit conversions.

  Write sql statement to create user defined function

Write SQL statement which creates user defined function which returns int value. This int return value is obtained by deducting the lowest Count field value from the highest Count field value of Part table.

  Write a pl/sql block and declare a variable v_sal

Write a PL/SQL block and declare a variable  v_sal  of the type NUMBER. Include the following statement in the Execution section:

  Design new transaction by sql statements

Code SQL statements to update view as follows: change spelling of Mark Tobey to Mark Toby. Design new Transaction for Mark Toby. Suppose you have essential transaction, work, and customer data.

  Write sql statement to create view

Write a SQL Statement to create a View called vwEmployeeTerritories that will do the following: Return all Employees and their Territories (use INNER JOIN).

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