Explain what is meant by the process of denormalization

Assignment Help Database Management System
Reference no: EM131372166

Relational databases: theory and practice Assignment

You are required to use the course software (MySQL) to answer all SQL questions. It is not permitted to use other SQL environments for this TMA.

ANSWER ALL QUESTIONS:

Question 1: Use the block notes to answer the following questions:

i. Explain what is meant by the process of denormalization and how it is done.

ii. Explain why we may need to use denormalization (i.e. provide the motivation for denormalization).

 iii. Explain when it is  a good idea to use normalization.

iv. Give an example of denormalization.

v. Explain the drawbacks of denormalization.

vi. Explain when denormalization should be avoided.

Question 2: Use the e-library and other resources to answer the following questions:

i. Explain what is meant by extensible relational database design.

ii. Explain what is meant by the "Entity-Attribute-Value (EAV)" approach.

iii. Explain how can the EAV approach be used to implement database extensibility.

iv. Give an example of using the EAV approach to implement a simple relational database with extensibility features.

v. Explain why we may need to use the EAV approach to implement relational database extensibility (i.e. describe the advantages of the EAV approach for implementing relational database extensibility).

vi. Explain why we may not sometimes wish to use the EAV approach to implement relational database extensibility (i.e. describe the disadvantages of using the EAV approach for implementing relational database extensibility).

You must provide at least two references in the Harvard style of referencing, excluding your course materials and Wikipedia.  At least one of the references should be a recent reference (within the last four years).

Question 3: Develop a conceptual model for the following scenario.  The data model should consist of the usual 5 components: E-R diagram, Entity Types (including entity type identifiers), assumptions, additional constraints, and limitations:

It is desired to develop an online pizza ordering database for a pizza shop.

For each customer, we need to keep a customer id, first name, middle name, last name, title, telephone number and address.

A customer may make zero or more orders.  For each order, we maintain a unique order id, a date, a time, and any special discounts offered on the whole order.

Each order is made by exactly one customer, taken by exactly one employee, delivered by exactly one driver and consists of one or more line-items.

An order cannot contain more than 100 line items.

For each employee, we keep an employee id, a first name, a middle name, a last name, an address, a telephone number and a salary.

An employee can take many orders.

For each driver, we keep the driver id, name, telephone number and hourly salary.

A driver may deliver many orders.

For each Line item, we keep a line number, a quantity, and a sale price.

A line item must be associated with a single order and must also be associated with a single menu item.

A menu item is a main item that can be ordered by itself from the menu.  There are two types of menu items: standard items, like standard specialty pizzas, French fries, salads, drinks, etc. and custom pizzas which you can customize yourself by choosing the crust type, the toppings, and so on.

For each menu item, we keep a menu number, a name, a description, a menu price, and customer special requests.

A menu item can participate in zero or many line items.  Every menu item must be either a standard item or a custom pizza but not both.

For standard items, we keep the menu number, which indicates that the menu item having this menu number is a standard item and the portion size (small, medium or large).

Obviously, a standard item must be associated with a single menu item.

For custom pizzas, we keep pizza-diameter, type of crust and flavor of crust.

A custom pizza must also be associated with a single menu item and can have many toppings up to a maximum of 7 toppings.

For each topping, we keep a type, pizza-diameter and price.  A custom pizza can only be supplied with a topping priced for the same diameter.

A toping may be used by many custom pizzas.

Note: you should expect about 10 entity types, 9 relationship types, 45 attributes, 8 additional constraints, 4 assumptions and 2 limitations in the CDM for this application.

Question 4:

i. Convert the following relational representation back into a CDM using three relationships and four entity types.  Include the ER diagram, the entity types and the additional constraint sections only.

ii. Consider the schema below to answer the following questions:

R (A, B, C, D, E, F, G)

 fd1: (A, B) → C, D, E, F, G

fd2: C → A, B, D, E, F, G

fd3: D → E

fd4: F → G

a. Show that relation R is in 2NF and not in 3NF explaining why.

b. Normalize the relation R into a set of 3NF relations. Show the primary keys.

c. Further normalize the results of the previous step into a set of BCNF relations if not already in BCNF and show the primary keys.

Question 5: You will need to refer to the University database to answer this question.  Write SQL queries to perform the following operations.  In each case show the SQL statement you used and the actual output from your DBMS.  You are required to show the actual output of your queries.

a) List patient details for all male patients in ward number 'w5'.

b) List the ward numbers of all occupied wards with no duplicates.

c) List the patient ids, the start date and reason for treatment for all patients undergoing a treatment for a reason that contains the word 'temperature' or the word 'fever' and who started their treatment after 5 December 2006.

d) List the names of all specialists along with their specialisms.

e) Get the ward numbers and ward names for all wards that have no patients.

f) Write a query to get the number of distinct patients undergoing each type of treatment (i.e. reason for treatment) in the database, ordered alphabetically by the reason for the treatment.

g) Write a query to get the names of all patients receiving a prescription whose daily dosage is higher than the average daily dosage of all prescriptions using the same drug.

h) Write a query to retrieve the ward number and average weight of patients for all wards having more than three patients, excluding ward w5.

Attachment:- Database Assignment.rar

Reference no: EM131372166

Questions Cloud

Member of protected class under the civil rights act : Lew, a member of a protected class under the Civil Rights Act, applies for a job with Lockheed Martin, but fails its employment test and is not hired. Lew believes that the test has an unintentional discriminatory effect. Under what theory might Lew ..
Probability that sample mean will fall within given : An economist wishes to estimate the average family income in a certain population.- What is the probability that the sample mean will fall within $800 of the population mean?
Discuss the division of labor according to gender : Discuss the division of labor according to gender, including the changes that occurred during the transition from nomadic to agricultural societies.Compare and contrast any TWO of the following societies, considering both their development and adv..
How well the company has addressed these two forces in past : Considering the five forces of competition, choose the two that you estimate are the most significant for the corporation you chose. Evaluate how well the company has addressed these two forces in the recent past.
Explain what is meant by the process of denormalization : M359 Relational databases: theory and practice Assignment. Use the block notes to answer the following questions: Explain what is meant by the process of denormalization and how it is done. Explain why we may need to use denormalization (i.e. provide..
Prepare a paper about organizational change : Your Project Assignment (due Week Four) is to prepare a paper about Organizational Change. You can talk about the reasons for change, resistance to change, overcoming and dealing with change, and/or managing change. The choice is yours.
The boat is seriously defective when delivered : Montre Vessels, Inc. (seller) and Lunar Harbor, Inc. (buyer) enter into a contract for the sale of a barge. Oceanic is a merchant that sells boats but does not manufacture them. The boat is seriously defective when delivered, and Lunar Harbor wants t..
Forecast a comparison of the population growth : From the e-Activity about the "Annual Population Estimates 2000 to 2009," analyze and forecast a comparison of the population growth rate from 2010 through 2016 of the State of Michigan and your state. Provide these figures in your response for ea..
What is the probability : If a random sample of 36 companies in the S&P 500 is selected, what is the probability that their average return for this period will be between 12% and 15%?

Reviews

len1372166

1/26/2017 11:56:41 PM

This section contains general rules and guidelines for completing and submitting your TMA. You are required to submit your TMA through the Learning Management System (LMS) provided by your branch. In case there are additional files to be submitted together with your TMA, you need to put all the files in a single directory and compress it into one .rar archive and submit it by the Cut-off date. Submit your TMA to the LMS system on (or preferably before) the cut-off date shown above. Your tutor will mark your script and post the grades to the LMS. You are required to use the course software (MySQL) to answer all SQL questions. It is not permitted to use other SQL environments for this TMA.

Write a Review

 

Database Management System Questions & Answers

  Determine the commissions paid to specific employees

Design a query that will allow the finance department to determine the commissions paid to specific employees of the sales department for the month of December.

  Create a data model for on adult baseball league

Create a data model for on Adult Baseball league which supports all of the following data requirements. Create an example model of this relationship using the MySQL Workbench data modeler.

  Develop an eer model using the traditional eer notation

An international school of technology has hired you to create a database management system to assist in scheduling classes

  Taskcreate a database design specification enhanced entity

taskcreate a database design specification enhanced entity relationship diagram eerd and relational data model rdm from

  Creates and populates the local db2 database

Populating Local DB2. Run the script that creates and populates the local DB2 database.  It has been posted on Bb, under Homework Assignments. Download it, study the relational schema and run it on your Oracle account

  Create the rdm with appropriate attributes

Details about each payment relating to a service, amount of payment and date need to be recorded - Create the RDM with appropriate attributes, primary and foreign keys, based on all the entities.

  What dimensions of information would you include

Consider the issue of timely information with respect to the businesses discussed in the case. Which of the businesses must have the most up-to-date information in its data warehouse?

  Write a script that includes two sql statements code

Write a script that includes two SQL statements coded as a transaction to delete the row with a customer ID of 8 from the Customers table. To do this, you must first delete all addresses for that customer from the Addresses table.

  Design a flowchart or pseudocode

Design a flowchart or pseudocode for the following:A program that accepts a candy name (for example, "chocolate-covered blueberries"), price per pound, and number of pounds sold in the average month, and displays the item's data only if it is a be..

  Why is it preferable to use a numeric based attribute

Why is it preferable to use a numeric-based attribute as the key attribute?- Why is it important that you identify all of the important relationships when developing an entity-relationship (E-R) diagram?

  Why wont this query return any results?

Why wont this query return any results?

  Implementation of virtual private databases

Prepare a 3-4 pages of technical document in MS Word Format on usage, utilization, and implementation of Virtual Private Databases (VPD) for the cases of your choice.Explain each situation in details, and describe how it works?

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