Reference no: EM133676811

Data Modelling and Database Development

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.