CST2355 – Database Systems

Assignment 1: Setting up Databases (20%)

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

Objectives of the Assignment

This assignment is focused on you familiarizing yourself with three database tools and going through the database design process. You will create a database using the out-of-box tools for Microsoft SQL Server, Oracle, and MySQL. You will submit a report explaining your approach, requirements, diagrams, and SQL used for creating those three databases. Your audience for this report include, management, technical people, and programmers. Management makes decisions for coordinating work. Technical people make decisions for design. Programmers make decisions for implementation. This Assignment is to simulate the work environment for a Canadian based company.

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.

This Assignment has asked you to create three new databases, one for each of the database technologies you’ve covered so far in this course:

Microsoft SQL Server
Oracle; and
MySQL

You were also handed over the following data → scribbles on 8 ½ x 11 pages, napkins, and the backs of customer receipts. They are for one hotel based in London Ontario. Notice that some data is missing. How are you going to handle it? Defaults? Manually entering data? You will need to update your approach & assumptions accordingly.

Bob Smith is the manager of the hotel with 20 years’ experience. He makes $80,000 per year and lives in Windsor Ontario. His manager is Deena Donor.
Bob Smith is the desk clerk with only 5 years of experience at the hotel. He makes $15 per hour and lives in Toronto Ontario. His manager is the other Bob Smith.
Tanya Duncan does maintenance and has been with the hotel for 10 years. She makes $5,000 per month and lives in London Ontario. Her manager is Bob Smith.
Kristoff Kurn was a customer that reserved a room Jan 3^rd and schecked in on Feb 3^rd. She checked out on Feb 17^th. She paid a deposit of $50 and her room rate per day was $50. She was a smoker so she was charged $25 for smoke damage and $30 for the carpet burns. She lives in Vancouver, British Columbia
Billy Elliot was a customer that reserved a room online the same day he checked in. He checked in on Jan 20^th at 4pm and stayed for one night. He is a student at Queens University and paid the student discount rate of $30 per day. Because he is a loyal customer he didn’t need to pay a deposit. He lives in Toronto. He used to live in Winnipeg Manitoba.
Justin Hackman is in sales and travels back and forth from Detroit and Toronto. Because of his frequent stays at the hotel he doesn’t need a reservation and pays a discount rate of $35 per day and a deposit of $20 because he is a smoker. He lives in Woodstock and stayed at the hotel overnight on Feb 2^nd, 8^th, 17^th, and 28^th.
Deena Donor is the operation manager of the hotel. She oversees the hotel by coming, in person on the 1^st of every month. She has been doing this from January through to April. Because of her position, she is not charged a deposit or room rate. When she does checkout the desk clerk sends the invoice directly to Van Whinkle for reimbursement.

Here are the queries you are asked to create using SQL. You do not need to include the results of those queries. All queries be a single TOP LEVEL queries.

Any Blanks. For each row retrieved, show all customers that have a blank in at least one of its columns. Sort from Z to A on last name then A to Z on first name.
Number of Stays. For each row retrieved, show the customer name and number of days stayed at the hotel where guests stayed at most 2 days, stayed exactly 4 days, and stayed more than 6 days. The number of stays MUST be in ONLY one column and calculated using the query. If a customer stays at a hotel for 3 days, leaves and comes back for 2 days, the customer DID NOT stay for 5 days. The customer stayed for 2 days and for 3 days.

FirstName	LastName	NumberOfDaysStayed

Expenses including Room Rate. For each row retrieved, show the customer name, each expense amount and type, and the total for these expenses when total expenses (including room rate) are larger than $50 for a customer. Calculate using a subquery the total as the total expenses for a customer’s stay. Sort by highest expenses to lowest. The query MUST NOT change when
- Customer pays more than one deposit during a stay
- Customer’s room rental rate changes each day during their stay
- Expenses and fees not mentioned in the assignment are later added to the database

FirstName	LastName	ServiceType	Amount	TotalAmountForStay

Expenses excluding Room Rate. For each row retrieved, show the customer name, each expense, and their total expenses when total expenses (excluding room rate) are larger than $50. Sort by highest total expenses to lowest. Calculate using a subquery the total as the total expenses for a customer’s stay. Sort by highest expenses to lowest. The query MUST NOT change when
- Customer pays more than one deposit during a stay
- Customer’s room rental rate changes each day during their stay
- Expenses and fees not mentioned in the assignment are later added to the database

FirstName	LastName	ServiceType	Amount	TotalAmountForStay

Employees. For each row retrieved, show the employee’s name, their city, their manager, and their manager’s city. DO NOT duplicate employee/manager names in your tables. It is okay to duplicate the names in this query.

FirstName	LastName	City	BossFirstName	BossLastName	BossCity

From London and Winnipeg. For each row retrieved, show which employees OR customers are from London and Winnipeg using the words London and Winnipeg in the SELECT statement. Use ONE operator in a WHERE clause that checks which city the employee is in and ONE operator in a WHERE that checks which city the customer is in.

FirstName	LastName	City

From Cities. For each row retrieved, show which employees OR customers are from specific cities. Do this by using a CITYLIST table to filter the query’s results. The CITYLIST table contains ONE column named City. This CITYLIST table contains only the cities you want to filter this query with. Each row in the CITYLIST table includes show ONLY the cities that the user wants to see in the query results. Use ONE operator that checks which city the employee is in using a WHERE clause and ONE operator that checks which city the customer is in using the WHERE clause. DO NOT use JOINs.

FirstName	LastName	City

Employees are Customers. For each row retrieved, show which employees are also customers

FirstName	LastName	City

Employee Customer M C. For each row retrieved, show all employees data and their corresponding customer data for employees that have last names starting with M or have a C in it. Customer data includes expenses, deposit, invoices, and etc.

First Name	Last Name	Position	Wage Type	Wage	City	Check In	Check Out	Service Type	Amount	Total Amount

Take note that you have also heard the owner plans on purchasing golf courses, casinos, and day spas in the near future, all of which will carry the Van Whinkle brand name.

Consider these when determining your table structures.

Hints

Think about what you’ve seen on invoices, your bank statement, transcripts, and how you enter data online. How do you think that data is stored. Here’s an example of a bank statement.

Date	Description	Amounts Debited From Account ($)	Amounts credited to your account ($)	Balance ($)
Jan 10	Cheque Deposit		$1,000.00	2,000.00
Feb 12	Online Transfer	$700.00		1,300.00
Mar 11	Online Payment	$400.00		900.00
Mar 24	Mobile Deposit		$500.00	1,400.00

Here’s a subset of a telecom bill

Type of Service	Amount	Date
Internet	$25.00	Jun 30
Cable	$30.00	Jun 30
Mobile Phone	$50.00	Jun 30
2 Year Promotion	– $10.00	Jun 30

And here’s an example of a sales receipt from the grocery story.

Item	Price
GROCERY
Diet C-UP	0.99
Pepsi Bottle	2.29
MEAT
Chicken	5.56
Steak	13.78
PRODUCE
Gold Apples	4.98
Cucumbers	4.88
Cucumbers	4.88
DISCOUNT	– 2.00

The same table structure was used for all three. What table structure do you think were used?

Instructions

Use the concepts and exercises you covered in Modules 1, 2, and 3. You will not receive marks using concepts outside these modules. (That means you do not specify the primary and foreign keys in the models or table creation). Create the three databases best suited for the data and the queries requested. Use both the visual tools and SQL script windows. Save all of your work, as you will need to use them later for this assignment.
Ensure that you also run your queries to confirm they work.
You will create a single technical document for your tables, diagrams and SQL scripts. Use all the work you saved to create the technical document. The sections of the document include:

Table Data (5%) – From ONE database include screen shots that show the columns for each table along with its rows of data. The same kind of data can be found in one table. There should NOT be any duplicate data except for fields used for cross-referencing aka JOIN-ing. Follow the standard naming practices covered in this course. (UPPER_CASE table names and ProperCase field names.) Do not use auto calculating fields in your tables. And do not store any data that can be easily recalculated by using a query.
SQL Queries (55%) – List each query’s name and SQL statements using ANSI SQL. Use only one top-level SELECT statement for each query and be careful to not create Cartesian products. You can use multiple subqueries. Copy and paste your SQL in the document. Reformat it so it is easy to follow and read. Use the format of the examples in Brightspace. DO NOT provide screen shots. Use ONLY SQL covered in the course so far. Finally, provide an explanation of the specific differences between each database’s SQL statement for SQL Server, Oracle, and MySQL. DO NOT provide the SQL for every database. Specify in layman’s terms what the differences are in syntax and statements. Be specific.
Databases
- SQL Server (10%)
  - Database design diagram – Include a screen shot of the database design diagram produced by the tool. Make your screen shot clear and easy to read. Format your screen shot to show only the diagram excluding the browser and toolbars. DO NOT include the CITYLIST table in your diagram. DO NOT create lookup tables or tables that store default information.
  - Table Structure – Screen shots of the designer windows used to design and modify each of the tables. DO NOT submit any substitutions. Substitutions can include CREATE TABLE statements, design diagrams, and hand made charts.
- Oracle (10%)
  - Database design diagram…
  - Table Structure…
- MySQL (10%)
  - Database design diagram…
  - Table Structure…
- Formating (10%) Text and Images are straightforward and easy to understand.
- Appendices.- Include all work not specified by the assignment in this section.

Submit your technical report as a PDF. To achieve a higher score please review this assignment’s rubric. Please include your name, your student number, section, and the name of the assignment. DO NOT submit your work as separate files. Please embed your diagrams, informative screen shots, and SQL code into a single PDF document. If you do not follow instructions or submit unreadable work, you will receive zero marks for that work.

To submit this assignment, submit your file as a PDF File, using the assignment upload tool in Brightspace. To access this, navigate to the Activities/Assignments link in the left-hand sidebar, and select Assignment 1: Setting up Databases.

Please note: Algonquin College has a policy regarding Plagiarism. The consequences are immediate zeroes for assignments and potentially an instant fail for the whole course. It is only natural for students to work together to complete assignments.

DO NOT PLAGIARIZE each other’s work.

Assignment 1 Grading Rubric (20%)

Table 1 – Assignment 1 Grading Rubric

	Exemplary	Accomplished	Developing	Incomplete
Criteria	3	2	1	0
Tables Data 3 points	Tables and columns followed naming practices AND All data was captured in the tables AND No data was duplicated across tables AND All data that can be determined or calculated from queries is EXCLUDED AND Screen shots were legible	Tables and columns did not follow naming practices	Not all data was captured in the tables OR Data was duplicated across tables OR Included data that could be calculated from queries	Not completed OR Screen shots were not legible
Queries 27 points 9 queries x 3 points	Included correct fields, tables, and filters AND Included correct grouping, aggregates, and functions AND Included correct subqueries, joining, and unions AND Included a description of how the SQL is different for SQL Server, Oracle, and MySQL	Excluded a description of how the SQL is different for SQL Server, Oracle, and MySQL OR Didn’t follow standard naming practices	Included incorrect fields, tables, and filters OR Included incorrect grouping, aggregates, and functions OR Included incorrect subqueries, joining, and unions OR Substituted table data for query processing	Not completed OR Query was was not titled OR Did no use ANSI SQL OR Was not a ONE top-level query OR SQL were images
Database Design Diagrams 9 points 3 diagrams x 3 points	Tables and columns followed naming practices AND Included all tables and fields AND Screen shot was legible and EXCLUDE browser and tool bars	Screen shot included browser and tool bars	Tables and columns did not follow naming practices OR Did not include all tables and fields or had duplicated fields	Not Completed OR Screen shot was not legible
Table Structures 9 points 3 screen shot groups x 3 points	Tables and columns followed naming practices AND Included ALL tables and fields AND Screen shots were legible and EXCLUDE browser and tool bars	Screen shots included browser and tool bars	Tables and columns did not follow naming practices OR Did not include all tables and fields	Not completed OR Screen shots were not legible
Text Content 3 points	All text was legible and well formatted	Text was not properly formatted	Text was legible in only some instances	Text was not legible in most instances.
Images 3 points	All images were legible, well organized, and did not include extra information	Objects in screen shots were not organized	Screen shots included extra information	Screen shots were not legible
TOTAL 54 points

End of Assignment