Assignment: M-13-Database Design Phase 2

Purpose:

In this assignment, you are to take your database notation from Module 12 ( (Provided below page) part 4 of your report) and, if necessary, correct it based on the annotation feedback and comments. Database design is an iterative process. Your tables must be normalized to 3NF, and they must be correct in order to make a good ERD for Module 14, Phase 3. In addition, you are going to provide sample rows of data for each table. This sample data is important for instructors to know what type of data is going into a column (it can affect normalization); it is not always clear from your proposals or notation. This is also a chance to seriously consider complications that may arise in data based on real-world situations that may occur. (For example, in the hotel example given in theModule 12 assignment, what happens when the hotel needs to record the name of more than one guest for a reservation?)

###########

Hotel example:

I am planning a database for a hotel database that stores details about employees, customers, reservations, room types, and departments. For reservations, I want to track the guests who reserve the rooms, the employee who confirm the reservation, the reserved room number, and the reservation date. For rooms, I want to store information about the bed size(s), etc etc

1. Business Rules

• A department has one or more employees but an employee belongs to only one department.
• Each room has one or more beds.
• etc etc...

1. Queries

• A manager needs a list of rooms that have twin beds.
• A guest needs a room that can hold more than 4 people
• etc etc...

1. Tables

department (departmentID, deptName)

employee (employeeID, empFirstName, empLastName, *departmentID, etc etc)

guest (guestID, firstName, lastName, etc etc)

reservation (reservationID, *customerID, *employeeID, *roomID, fromDate, toDate)

room (roomID, occupancy)

roomBed (*roomID, *bedID)

bed (bedID, *bedTypeID)

bedType (bedTypeID, bedDescription)

etc etc

###########

Resources needed:

• • Annotations and comments (feedback) given to you in Module 12

Skills:

This assignment will focus on your ability to understand normalization and refine your database based on feedback and upon your further developments.

Knowledge:

The skills necessary to adjust when developing a database project are essential for satisfying clients' needs.

Task Phase 2: Normalization

In this assignment, you are to take your database notation from Module 12 (part 4 of your report) and, if necessary, correct it or revise it based on the annotation feedback and comments.

Note:

In addition, you must provide at leasttwosample rows of data for each table.

Submit your corrected database notation and sample data as a.pdfnamedmodule13.pdfand upload it when finished.Add this sample data below your set of normalized tables, like this:

department(departmentID, deptName) item (itemID, *departmentID, description, price, status)

etc...

departmentID	deptName
1001	Security
1002	Maintenance

itemID	*departmentID	description	price	status
500	1005	hammer	5.80	Y
510	1002	digital camera	765.50	N

##########

Criteria for success:

• You have addressed the annotations and comments made by the instructor(s) in Module 12.
• Your tables are normalized to 3NF.
• Your sample data is indicative of what data will be in the tables.

Here is the table and the business descriptions I did for M 12:

Module 12:

Abstract My database has information on an auto shop, including customer tables with personal data such as name, address, and phone number. The vehicle's table will differentiate each vehicle that comes in for service or repair with information such as the vehicle identification number (vehicleID), vehicle identification number (vin), and a text entry for notes on the problem or malfunction requiring Attention. Using employeeID from the employee record, the database must additionally monitor which car each employee is working on. Business Rules: One or more auto shops can service a given vehicle. A car has a single and unique VIN A worker can work on multiple automobiles. One license plate can only be displayed on a single vehicle. A customer may have multiple automobiles. One model might be under multiple vehicles and vice versa. Queries: A worker must require a list of the number of automobiles undergoing maintenance or repair at the shop. License plate or VIN are used to search the database for a car and customer. A worker must compile a list of the number of automobiles a customer owns. To search the database for a car and customer, workers use License plate or VIN. Tables: customer (customerID, firstName, lastName, phoneNumber, customerEmail, preferredContact, companyNumber) autoShop (autoshopID, autoshopAddress, autoshopCity, autoshopState) employee (employeeID, employeefirstName, employeelastName, hireDate, position, shift) vehicle (vehicleID, *customerID, *autoshopID, licensePlate, vin, manufacturedYear, *modelID carIssue) vehcileType (vehicletypeID, typeName) model (modelID, modelName, *makeID, *vehciletypeID) make (makeID, makeName, color)