Reference no: EM133070553

COIT20247 Database Design and Development - Central Queensland University

Assessment: 1. ER Mapping and Normalization

a) Map the ERD, from the sample solution, into a set of relations in at least Third Normal Form (3NF). You must ensure that your relations meet 3NF. There is no need to show your working.

b) Select any one of your relations from the previous step, 1a), and perform the following:
• List all the functional dependencies exist in the relation.
• Demonstrate that the relation meets Third Normal Form (3NF).
For your help, an example for a Student relation's 3NF justification has been provided below:

STUDENT (StudentID, Name, Address,DateOfBirthThe primary key is StudentID which identifies a student.

1. There will be one student name per Student ID because only one name is recorded for each student. Even though a student may have multiple addresses (e.g. a home address and a work address), the case study states that there is a need to record the home address only for each student and hence there will be only one address for each student. There will be only one date of birth for each student. Thus, there are no repeating groups. Hence, this relation is in 1NF.

2. The primary key is StudentID. This is the only candidate key since student name is not guaranteed to be unique (two different students may have the same name). Student address is also not guaranteed to be unique. This means that Student ID functionally determines every other attribute in the table. The candidate key, StudentId, is a simple and single valued attribute. Therefore, no partial dependencies are possible. Hence, the relation is in 2NF.

3. Student name cannot be used to functionally determine any other attribute in the table since two different students may have the same name: likewise, for address and date of birth. Therefore, there are no transitive dependencies in the table. Therefore, this relation meets the requirements of first, second and third normal form.

Question 2. Use MySQL Workbench to create the logical database model

Consider the E-R model on the theater booking as the solution of assignment 1 released, Use MySQL Workbench 8.0 CE to create a logical database model with the relations as in Part 1(E-R mapping and normalization), include specifying the primary keys, foreign keys, creating relationships as appropriate, and creating the E-R diagram. Save the file as fbdb.mwb.

Question 3. Relational database implementation using MySQL Server

Note:Use MySQL Server to perform the physical implementation of the designed database from the above logical model and export the database file as fbdb.sql.In implementing the ER model provided, you must complete the following tasks:

• Create all the relations in a MySQL database. Consider each attribute in every table and make appropriate choices regarding data types & sizes, null allowed or not. Your choices should be appropriate for each attribute and should support data integrity.
• Indexing can speed up search for tables or queries. Review the guideline given in the textbook regarding set-up of index. You mustensure that at least the Name and Speciality attributes in the Surgeon table, as well as the Lease Booking IDattribute in the Lease Booking table are indexed.
• Populate the database with sample data of your own. You must include sufficient sample data to test your queries and report. Please note that the expected result of query questions as below should depend on the actual sample data populated.

You can also implement your database using MySQL Workbench or via Command Line Client. (Note: Week 6 lecture and tutorial provide the instructions how to export a MySQL database file)

Stored procedures

In databases, a stored procedure is a piece of prepared SQL code that you can save, execute and reuse at any time. You are required to write two stored procedures as specified below:

a) Write a stored procedure that can be used to show the details of each operation theatre booking when it is confirmed. The details include the lease booking ID, booking date, theatre ID, room number, cost per day and total cost.

b) Write another stored procedure with a parameter that represents the theatreroom. When the stored procedure is called, the value of parameter is passed to the procedure. The procedure will display the surgeon's name, his/her specialty, and the booking ID.

Information requests

Create queries to answer the following information requests. You should type the query codes manually using SQL view/editor under MySQL Server.

The marking process of the queries for information requests may also consider the effectiveness of your SQL statements that have been used in the queries. It is important that your SQL statement must reflect the correct business logic & SQL syntax. (Use enough sample data so that the output is produced by each and every query. The expected result is under each question. Your result should be in a similar format but may have a different content, depending on the data you populated in your database). Under MySQL Server, save each query question SQL statementas a file such as q1.sql, ..., q5.sql.

Note:Use enough sample data so that the output is produced by each query.Depending upon the data in your database, your query output will differ from the following outputs:

1. Obtain a listof ID and Name of support staff who have not been allotted to any lease booking as on date.

SupportStaffId	Name
5	Daniel Busker

2. Display the number of lease bookings, if any, that have been done for each surgeon.

SurgeonId	Name	NumBookings
1	John Lewis	2
2	Lisa Major	1

3. For each lease booking, display surgeon id, surgeon name and total amount of lease including break down costs.

LeaseBookingId	SurgeonId	SurgeonName	TheatreCost	SupportStaffWagesTotal	LeaseTotal
1	1	John Lewis	$700.00	$440.00	$1,140.00
2	2	Lisa Major	$700.00	$200.00	$900.00
3	1	John Lewis	$740.00	$0.00	$740.00

4. For each lease booking, display lease booking id, total amount of lease, total amount paid as on date and pending (outstanding) amount.

LeaseBookingId	LeaseTotal	Paid	Pending
1	$1,140.00	$1,100.00	$40.00
2	$900.00	$900.00	$0.00
3	$740.00	$700.00	$40.00

5. Display the id and name of theatre(s) for which the most (the highest number of) lease bookings have been done as on date.

TheatreId	TheatreName	NumBookings
1	East	2

Report

MySQL Server doesn't come with a native Reporting tool. But it is very easy to create a Report by export the data source of a MySQL database to a third-party utility program (such as phpMyAdmin, dbForgeStudio, and Navicat etc.) or Microsoft Excel. As an exercise, we ask you to export the data source to Excel to create a simple report object showing the details of each lease booking and payment details. When the data source from MySQL exporting to excel, simply call and execute the first stored procedure as defined in the relational database implementation section on a sheet of the Excel, then import the data (due to the result of executing the stored procedure) to Excel. In addition, add a report header and display the total price of all bookings in a cell on the Excel sheet. (Note: when you install MySQL Server, if have not installed the component named mysql-for-excel-1.3.8, you need to download and install it separately. The installer is also available on the Moodle course website).

Your report will also show the details of each surgeon and his/her respective lease booking(s)and payment details. The report should show the surgeon's name, total charges for each lease, amount paid and outstanding amount (pending) details.

Implementation Report

You must also provide a short implementation report describing your experiences with this assignment. You need to discuss on the following:

1. Any one of the assignment tasks that was relatively difficult/complex to solve but has been successfully completed by you (you should mention the aspects from your database and should not write general report).

2. The problems that may arise when your database is in operation and suggestions to improve the database particularly table design, data entry, query formulations, etc.

Use the implementation report to demonstrate that you have thought carefully about the issues that arise when implementing a relational database.

What you should submit

Place the following files in a zip file named Ass2.zip and submit via Moodle Assignment 2 link.

1. MySQL Workbench logical database model file - fbdb.mwb
2. MySQL database file - fbdb.sqlcontaining:
a. Create table SQL statements
b. Tables with sample data with insert SQL statements
c. Create indexes
d. The required stored procedures
3. The files of answering five information request questions (q1.sql,...,q5.sql)
4. A Microsoft Word document containing:
a. A set of relations mapped from E-R diagram (3NF)
b. Two relations with functional dependencies and demonstration of normalization
c. Implementation report

Your document should contain appropriate identification information (your student number, name, unit number, etc.) and should contain the relevant headings for each section.

5. An Excel file of a simple Report with data source imported from MySQL Server

Attachment:- ER Mapping and Normalization.rar