Database Systems

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:

  1. Microsoft SQL Server
  2. Oracle; and
  3. 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 3rd and schecked in on Feb 3rd. She checked out on Feb 17th. 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 20th 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 2nd, 8th, 17th, and 28th.
  • Deena Donor is the operation manager of the hotel. She oversees the hotel by coming, in person on the 1st 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

  1. 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.
  2. Ensure that you also run your queries to confirm they work.
  3. 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.
  1. 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