CST2355 - Database SystemsAssignment 1: Setting up Databases (15%)This assignment relates to the following Course Learning Requirements:CLR 1 - Plan, Prepare, Install, Configure, and Use a...

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 brand 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.
i. Verify the Oracle Database is installed and running
ii. Verify Data Modeler is installed
iii. Verify SQL Developer is installed and can connect to the Oracle database
iv. 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.
i. Create a new Database Design diagram
ii. 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
iii. Add a primary key ID column to the FEE table that has a maximum of 15 digits. Apply your changes.
iv. Rename the primary key constraint to FeePK. Apply your changes.
v. 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.
i. 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)
ii. 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.
iii. 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 correct parent and the correct 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 current date
Exclude menus, toolbars, and object browsers
    6
    Forward Engineering Script
i. Generate a SQL Script for your Database Design Diagram
ii. Remove all lines that prefix with hyphens (these are comments and are not needed)
iii. Add a comment on the first line that includes your name, student number, and current date. For the student Bob Clark the comment could look like…
-- Bob Clark, 12345678, 2022/07/01
iv. You should ONLY have the SQL statements starting off with: DROP, CREATE TABLE, ADD CONSTRAINT statements remaining
v. 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)
vi. 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
vii. 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 )
);
viii. To avoid compile errors, verify you have your commas, brackets, and semi-colons in the correct places.
ix. 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.
x. 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.
xi. Verify you have a semicolon after the closing bracket for each CREATE TABLE statement
xii. Save your SQL Script as a file
xiii. 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...