CST2355 - Database Systems
Assignment 1: Setting up Databases (15%)
This assignment relates to the following Course Learning Requirements:
CLR 1 - Plan, Prepare, Install, Configure, and Use a market-leading Database Management System, Data Modeling Engineering Tools, and Open Source Software.
CLR 2 - Develop Advanced Database Design and Normalization
CLR 3 - Develop advanced subjects and techniques of using the SQL database language
Background
You are a new employee at a startup company Van Whinkle. The company just purchased several mom and pop hotels that they want to incorporate under their Van Whinkle
and name. Because the company is new and the hotels were previously owned by small business owners, they do not have any electronic databases. Most have their data on paper. Only a few were a little bit more organized and had spreadsheets and QuickBooks.
Instructions
Execute the following steps. Please submit your work only where the instructions specify to do so. If you do not follow instructions, your submission will be rejected.. For this assignment use the tools specified in Activity 1.
Activity
1
Verify all Tools are Installed
Unlike other tools such as MySQL and SQL Server, the Oracle server and its needed tools are downloaded separately. The following steps downloads the Oracle database.
Verify the Oracle Database is installed and running
Verify Data Modeler is installed
Verify SQL Developer is installed and can connect to the Oracle database
Verify SQLPLUS is available from DOS and that you can log into using a user and password
2
Naming Standards
The common naming standard is having UPPER_CASE table names and ProperCase field names. This naming standard ONLY applies to your database design and the SQL your run in Oracle.It doesn’t apply to the the actual object names created in Oracle.
UPPER_CASE
(tables)
EMPLOYEE, PRODUCT, CORPORATE_OFFICE
ProperCase
(columns)
EmployeeID, LastName, SKU_ID
Underscores are only used in naming when they are absolutely needed to separate words for readability. For example, SKU_ID is easier to read than SKUID.
Note:
This naming standard applies to all your diagrams and when you write your SQL. Here’s an example of the naming for a SELECT statement.
SELECT FirstName FROM EMPLOYEE;
When creating your tables in Oracle, however, do not rename the default UPPER_CASE fields to ProperCase. Doing so will require you to add double-quotes to your column names for all your SQL.
SELECT “FirstName” FROM EMPLOYEE;
Notice the double quotes. This SQL statement will not run on other databases as is because of these double-quotes.
3
Add FEE table to Database Design
You will create a one table database design. This diagram is called a database design and NOT an E-R diagram. Because of the confusion it creates the term E-R diagram will not be used during this course.
Create a new Database Design diagram
Add a FEE table to your diagram. Name it FEE_NN where NN are the initials of your first and last name
Student Bob Clark would name the table FEE_BC
Add a primary key ID column to the FEE table that has a maximum of 15 digits. Apply your changes.
Rename the primary key constraint to FeePK. Apply your changes.
Add the following fields then save your changes.
Column Name
Data Type
Size
FeeDescription
VARCHAR
100
DateCharged
DATE
Column Name
Data Type
Precision
Scale
FeeCharged
NUMERIC
5
2
4
Add SERVICE and GUEST tables
The company wants to track what services were charged for which fees. To do this, a service table is created which will be a parent of the fee table. Also the company wants to track what guests were charged which fees. So you create a guest table that will be another parent of the fee table. You do not add guest information to your FEE table. The FEE table is only for fees and foreign keys to other tables. There is no other information needed.
Following the steps you used to create the FEE, create a SERVICE table and a GUEST table. Name them SERVICE_NN and GUEST_NN where NN is your initials. Save your changes each time you complete creating a table.
Table Name
Field Name
Data Type
Primary Key
SERVICE
ServiceID
NUMERIC (15)
Yes
ServiceName
VARCHAR (40)
ServiceFee
NUMERIC (5, 2)
GUEST
GuestID
NUMERIC (10)
Yes
FirstName
VARCHAR (20)
LastName
VARCHAR (20)
City
VARCHAR (20)
Province
CHAR (2)
Create a non-mandatory foreign key relationship line between the SERVICE (parent) table and the FEE (child) table. Rename the foreign key constraint and foreign key column to follow the ProperCase naming standard. Save your changes.
One guest is charged multiple fees. Create a non-mandatory foreign key relationship line between the FEE table and the GUEST table. Make sure you pick the co
ect parent and the co
ect child in the relationship. Rename the foreign key constraint and foreign key column to follow the ProperCase naming standard. Save your changes.
5
Submit Database Design
Add below a screen shot of your Database Design diagram. To receive full marks your diagram must includes your tables, name, and student number with the cu
ent date
Exclude menus, toolbars, and object
owsers
paste your screenshot here
6
Forward Engineering Script
Generate a SQL Script for your Database Design Diagram
Remove all lines that prefix with hyphens (these are comments and are not needed)
Add a comment on the first line that includes your name, student number, and cu
ent date. For the student Bob Clark the comment could look like…
-- Bob Clark, XXXXXXXXXX, 2022/07/01
You should ONLY have the SQL statements starting off with: DROP, CREATE TABLE, ADD CONSTRAINT statements remaining
Rewrite this script so table names are UPPER_CASE and columns are ProperCase (this allows you to reuse the script to run on different databases)
Remove CASCADE CONSTRAINTS from the DROP statement by making the statement look like the following
DROP TABLE TABLE_NAME;
As a practice you drop one constraint at a time so you can easily backtrack and undo mistakes
Merge the ALTER TABLE statement with the CREATE TABLE following the below patterns. (this improves the readability and understandability of the script)
-- PARENT
CREATE TABLE TABLE_NAME (
TableNameID,
…
LastField,
CONSTRAINT TableNamePK PRIMARY KEY ( TableNameID )
);
OR
-- CHILD
CREATE TABLE TABLE_NAME (
TableNameID,
…
LastField,
CONSTRAINT TableNamePK PRIMARY KEY ( TableNameID ),
CONSTRAINT ConstraintName FOREIGN KEY ( ParentID ) REFERENCES ChildTableName ( ChildTableID )
);
To avoid compile e
ors, verify you have your commas,
ackets, and semi-colons in the co
ect places.
Reorder the DROP statements so the child table (FEE) is dropped before the parent tables are (SERVICE and GUEST). Parents cannot be successfully dropped before their children.
Reorder the CREATE statements so the child table (FEE) is created after the parent tables are (SERVICE and GUEST). Children cannot be successfully created before their parents.
Verify you have a semicolon after the closing
acket for each CREATE TABLE statement
Save your SQL Script as a file
Run your Forward Engineering Script to create your SERVICE, GUEST, and FEE tables.
7
Submit Forward Engineering Script
Add below a screen shot of ONLY your DOS window. To receive full marks your screenshot must includes all SQL statements with its results along with your name, student number, and cu
ent date. Maximize your DOS window before taking the screenshot.
paste your screenshot here
8
Submit Table Designer Screen Shot
Add below a screen shot of the FEE table design proving that it was successfully created in the database. To receive marks your screenshot must includes your table name and ALL of its columns, data types, and attributes.
paste your screenshot here
9
Add Data to the GUEST, SERVICE, and FEE tables
You keep all guest names and information in the GUEST table. You do not duplicate this information in any other table. The more duplicates of information, the more processing power and network bandwidth you need to use to ensure all duplicates are exactly the same. For each duplicate you face a cost.
Activity
Cost
Managing where duplicates are located
More documentation and more scripts that need to be maintained and troubleshooted
Ensuring all duplicates are exact copies
Maintaining scheduled batches and triggers with complex e
or handling to ensure data is kept in sync
Avoiding dirty reads
Adding logic and constraints to ensure the duplicated data being read is co
ect and up-to-date.
Add the following data to the GUEST table, Remember to commit your entries one done.
- Kristoff Kurn lives in Vancouver, British Columbia
- Billy Elliot lives in Winnipeg, Manitoba
- Tanya Duncan works for the hotel and lives in Woodstock, Ontario
- Fred MacFadden lives in Ottawa, Ontario
- Deena Donor works for the hotel and lives in Toronto, Ontario
- Justin Hackman lives in London, Ontario.
Add the following data to the SERVICE table. Remember to commit your entries once done.
- Room Deposit, -$40 (deposit is a negative amount)
- Room Rate, $50
- Smoke Damage Repair, $60
- Carpet Repair, $70
Add the following data to the FEE table. Remember to commit your entries once done.
Kristoff Kurn was charged for Feb 3rd to Feb 17th. She paid a deposit of $50 on Feb 3rd.
(store one row for the deposit charged. The FeeDescription is Deposit Charged).
Her room rate per day was $50 from Feb 3rd to Feb 10th.
(store 8 rows of room rates charged. The FeeDescription is Room Rate Charged.)
And her room rate was $45 from Feb 11th to Feb 17th.
(store 7 rows of the room rate charged. The FeeDescription is Room Rate Charged.).
She was a smoker so she was charged $25 for smoke damage and $30 for the carpet burns on Feb 17th.
(The FeeDescription is Smoke Damage for one charge and Carpet Burns for another charge.)
Billy Elliot was charged last year on Jan 20th. He is a student at Queens University and paid the student lower rate of $30 per day.
Because he is a loyal guest he didn’t need to pay a deposit on Jan 20th.
(this means you need to store a record showing the guest was charged a deposit of 0.00)
Justin Hackman paid last year a discount rate of $35 per day and a deposit of $20 because he is a smoker. He was charged the room rate for Feb 2nd, 8th, 17th, and 28th
Deena Donor was charged last year on the 1st of every month from January through to April. Because of her position, she is not charged a deposit or room rate.
(this means you need to record the room rate being 0.00 and the deposit being 0.00 for each 1st of the month)
10
Submit GUEST, SERVICE, and FEE Data Screen Shots
Add below a screen shot of ONLY your DOS window. To receive full marks your screenshot must includes all SQL statements with its results along with your name, student number, and cu
ent date. Each row of data for each table must take a single row. 5 rows in the table should take 5 rows in the screen shot. Include ALL columns. Maximize your DOS window before taking the screenshot.
paste your screenshots here
11
Generate INSERT Statements
Generate your insert statements script for your FEE, SERVICE, and GUEST tables
Save your script file
12
INSERT Statements Screen Shot
Add below a screen shot of ONLY your DOS window. To receive full marks your screenshot must includes all SQL statements with its results along with your name, student number, and cu
ent date. Maximize your DOS window before taking the screenshot.
paste your screenshot here
13
Add STAY, EMPLOYEE, VENUE tables
A stay for a guest at a hotel is determined not by when they are charged but by when they formally check-in and check-out. Each time a guest checks-in, the database generates a new stay for the guest and associates all fees charged to that stay.
Employees charge guest fees. Unlike the other tables so far, the employee table uses a recursive relationship. A recursive relationship is where a table references itself. They are used to show hierarchies of projects, teams, or people. For employees, each employee reports to a manager.That manager, too, is an employee. Recursive relationships reduce tables and simplify queries since you don’t need separate tables for employees, managers, and their managers.
A venue is an organization where a guest can go and receive services. It can be a different hotel, casino, spa, or golf course.
Create a STAY, EMPLOYEE, and VENUE table. Name them STAY_NN, EMPLOYEE_NN, and VENUE_NN where NN is your initials. Save your changes each time you complete creating a table.
Table Name
Field Name
Data Type
Primary Key
STAY
StayID
NUMERIC (15)
Yes
CheckinDate
DATE
CheckoutDate
DATE
EMPLOYEE
EmployeeID
NUMERIC (15)
Yes
FirstName
VARCHAR (20)
LastName
VARCHAR (20)
Title
VARCHAR (20)
City
VARCHAR (20)
Province
CHAR (2)
Wage
NUMERIC (8, 2)
WageType
VARCHAR (20)
VENUE
VenueID
NUMERIC (15)
Yes
VenueName
VARCHAR (40)
One stay can involve multiple fees. Create a non-mandatory foreign key relationship line between the FEE table and the STAY table. Make sure you pick the co
ect parent and the co
ect child in the relationship. Rename the foreign key constraint and the foreign key column to follow the ProperCase naming standard. Save your changes.
One employee can involve multiple stays. Create a non-mandatory foreign key relationship line between the STAY table and the EMPLOYEE table. Make sure you pick the co
ect parent and the co
ect child in the relationship. Rename the foreign key constraint and the foreign key column to follow the ProperCase naming standard. Save your changes.
One employee can have multiple employees reporting to them