Perform physical design and implement a database

Assignment Help Database Management System

Reference no: EM133676539

Data Modelling and Database Development

Practical and Written Assessment

Assessment Objectives:

Part A
Students are required to use the knowledge obtained in this course to design and implement a relational database management system (RDBMS).
Students need to perform physical design, implement a database, and create SQL queries on the database tables to get the required outputs.

Part B
Students are required to critically analyze and investigate issues related to database industry trends and multiuser database environments.

Assessment Description:

Part A: Design and implement a RDBMS
Consider the following Case Study from AT2:

Bayside Council, Australia, is conducting inspections of the restaurants in the region to maintain the quality of their kitchens. The council has access to information about the proprietors of kitchens along with the kitchens' details. Each proprietor is assigned a unique Proprietor ID (identifier). Additionally, a proprietor has a Family Name, Given Name, Address (Street, City, State, Post Code) and Phone Number. Kitchens are categorized, and for each kitchen, details such as Kitchen ID, Business Name, Address (Street, City, State, Post Code), and Phone Number are maintained.

The council has employed inspectors who carry out these inspections. The system maintains the Names and Phone numbers of the inspectors. Each inspection is scheduled on a specific Date. If any violations of quality standards are detected in a kitchen, they are recorded with a Description, Required action, and a Due date to fix them. Moreover, violations are categorized into different levels, and each level has a Description and Number of days to fix the violation. When a business fixes the violation, the proprietor contacts the council so that the inspector can check on it and update the system with the fixed date.

Consider the following schema for the given case study.

Proprietors (ProprietorID, ProprietorFamilyName, ProprietorGivenName, ProprietorStreet, ProprietorCity, ProprietorState, ProprietorPostCode, ProprietorPhoneNbr)
Kitchens (KitchenID, BusinessName, KitchenStreet, KitchenCity, KitchenState, KitchenPostCode, KitchenPhoneNbr, ProprietorID, KitchenTypeID)
KitchenTypes (KitchenTypeID, KitchenDescription)
Inspectors (InspectorID, InspectorFamilyName, InspectorGivenName, InspectorPhoneNbr)
Inspections (InspectionID, InspectionDate, KitchenID, InspectorID)
Violations (ViolationID, ViolationDescription, ActionRequired, DueDate, Fixed, DateFixed, InspectionID, ViolationLevelID)
ViolationLevels (ViolationLevelID, ViolationLevelDescription, NumberOfDaysToFix)

Implement the database for the above case study. You may start with the Database Design Language (DBDL) documentation that you derived in the AT2 ERD Case Study Assignment. Make sure that the database is in 3NF.
Create a physical design of the tables with suitable primary keys, foreign keys, data types, etc.
Note: Write down the SQL queries that you used to create the tables. Make sure you use SQL commands (not QBEs) to create tables.
Populate your tables with meaningful data (at least 25 records per table) to demonstrate the results of the queries for the questions below.
Note: You don't have to include SQL queries that you used to insert data into tables. Instead, include screenshots of all tables with data (i.e., query outputs in datasheet view).

Create SQL queries to answer the following questions.
Note: For each question below, you must write down the SQL query and include screenshots of your query results. Make sure you create SQL commands (not QBEs).

List all the violations recorded in a specific region (from a single postcode of your choice).
List the kitchens located in a specific city (of your choice) along with the names of their proprietors.
How many violations were there on each inspection? Show the Inspection ID, the business name of the kitchen, and the number of violations. Order the list so that the inspection with the most violations appears first.
List the inspectors along with the number of inspections they have conducted.

Show the ID and name of any proprietor with a level 1 violation. That is, which proprietors own a kitchen that has been inspected and had a level 1 violation?
How many violations have been in each level over the last 12 months? Make sure the list is shown in level order.
Which kitchens have been inspected more than once in the last 12 months? Show the business name of the kitchen and the number of times it has been inspected.
List the kitchens that never had any violations.

How would denormalization benefit physical database design? Explain using the given case study.

Part B: Analyze and investigate issues related to database industry trends
Critically analyze and investigate issues related to database industry needs, database administration, and database management systems processes in the previous case study and write a report of 2000 words presenting your analysis and recommendations.

Include an Introduction, Body (give an appropriate title), Conclusion, and References. You may organize the Body with proper headings and subheadings as needed.

Reference no: EM133676539

Questions Cloud

Create a job description about accessing client needs : Create a job description about Accessing client needs and developing strategies to help the client. Evaluating and studying programs, services and resources.

Discuss personality change and cognitive decline is known as : A frontal temporal dementia would unset in the 5th and 6th decade of life more common in men marked by personality change and cognitive decline is known as?

Which actions would be appropriate : Your patient is admitted with a T-4 complete lesion. Temperature is 96.9 F, blood pressure is 80/44 mmHg. Which actions would be appropriate?

List three low potency steroid preparation : List three low potency steroid preparation, and write a prescription for one. List one medium and one high potency steroid preparation.

Perform physical design and implement a database : Perform physical design, implement a database, and create SQL queries on the database tables to get the required outputs

What type of altered mental status is most likely present : An older adult patient is brought to the emergency room by her family. What type of altered mental status is most likely present in this patient?

Panama canal expansion - understanding of project management : Panama canal expansion - understanding of project management (PM) theory/principles through analysis of a real case study (completed)

For which type of headache is it primarily indicated : Atogepant (Qulipta) is an oral calcitonin gene-related peptide (CGRP) antagonist used as a second-line. For which type of headache is it primarily indicated?

Which is a definitive diagnostic test for cholecystitis : Janet is a 70-year-old White woman who comes to see her APRN for pain in her abdomen. Which is a definitive diagnostic test for cholecystitis?

User Account

All Pages