(Solved): CIS 365 – Business Database Systems-NEW...

CIS 365 – Business Database Systems
W7 – Create FamCation Resort Database Tables (150 points)
This is an individual exercise, NOT a group assignment.

• Download FamCation table schema on Canvas in the Assignment folder
• Download FamCation record insert scripts on Canvas in the Assignment folder

Q1. Create FamCation Resort DB tables using GUI (20 points)
Use GUI (graphical user interface) to create CONDO, ACTIVITY, GUEST, and FAMILY tables based on the provided table schema.
Right mouse click Table, select New/Table

Q1.1 Provide a screen shot of each table you created along with the primary key, example below.

Q2. Create the rest of the tables, PERSONNEL, HOUSEKEEPING, GUIDE, CLEANING, BOOKING, RESERVATION by writing the query statement from scratch or using the insert snippet template.
Remove InvoiceID from the RESERVATION table. You don't need to create the INVOICE table. (60 points)
• The order of table creation is very important due to referential integrity!
• You can copy and paste scripts into or from SSMS.
• Remember to SAVE YOUR SCRIPTS
Q2.1 Provide a screen shot of each create table script in SSMS, example below

Q2.2 Provide a listing of all the tables you created. Example below.

View Object Explorer Details – before you insert any records, all the row counts are zero.
Q3. Insert data records to all the tables created in Q1 and Q2 by using the instructor provided scripts on Canvas. (Your table definitions in Q1 need to match the instructor provided table schema on Canvas, e.g., same data type and column position, for the insert statements to work!). (45 points)
Steps -
• Copy and paste the instructor provided scripts from the Word doc to the SSMS window editor. Execute the insert queries.
• After all records are inserted, review the SSMS message. During the insertion processing, three records could not be successfully loaded due to constraint violations.
Q3.1 Screen shot the error message where the violation occurred. Research the errors and explain the cause of the violations. You don't need to fix the data errors. Example below. (3x5 = 15 points)
Cleaning Table - referential integrity violation. 'something' is not defined 'somewhere'.
Q3.2 Take a screen shot similar to the following and match your record counts to the screen below. The row counts don't include the three problem records in Q3.1. (3x10 = 30 points)

Q4. Write your own script to insert 5 more 'salary' employees who are neither housekeepers nor guides to the PERSONNEL table. Enter your own name as the manager of the other four new employees. (25 points)

Q4.1 Screen shot your insert scripts for these 5 employees (15 points)
Q4.2 Screen shot your select query to display these 5 employees similar to the following. (10 points: select query 5 points, result 5 points)
Due Date: EOD Monday 10/11
• All your screenshots need to include your ASU ID on the top. Example below.
• Convert the entire document into one pdf file for submission.
• Save your file name as HW7-Student Name.pdf.
• Submissions must be posted to Canvas to be graded