MGMT 329 – Spring 2021, Homework #3

DUE Monday, April 5, 2021 at 11:59 PM

This assignment uses a new database, called COVID-19 Vaccines. The concepts that you learned from the first two assignments are immediately applied.

Your goal for the assignment is to create the database on a SQL Server DBMS using Visual Studio and the Anderson VLAB. If you are not familiar with the VLAB, review the “Connect to the DB” video, which describes how to use the VLAB and how to connect to your database on the server.

You will be required to build the entire database as defined in the supplied table descriptions, ERD and assumptions. You will also be required to create data to insert into all tables of the database.

You will be programming, and will be using a dedicated database as your final solution. You must submit your own code based on your assigned database. You WILL be tested on your knowledge of the material for the second exam, and so it is your responsibility to know how to do what is required of this assignment.

Your code should be able to successfully create a “shell” for the database as well as create data that will be inserted into the database. Your code will be tested using the methods discussed in lectures. Your code will be graded according to how it functions as well as usage of the proper methods to implement the pieces of the database. Review all lecture videos to grasp the information needed for this assignment. If necessary, schedule a time (early) with the instructor to review your code BEFORE you submit.

DO NOT START THIS ASSIGNMENT A DAY PRIOR TO THE DUE DATE!

YOU WILL NOT COMPLETE ON TIME!!! Do a piece each day.

Assignment Requirements

You are required to do the following:

1) Create the database using the database on the designated SQL Server. Server, database and account information will be sent to you in UNM Learn. Refer to the demo video in the VLAB section of the course site to learn on how to connect to your databases.

2) Create the entire database and its tables, relationships, and constraints in the database.

3) Insert data into the database.

4) All tables, constraints, and data must be coded! Do not use any “wizards” or GUI’s tools to create your objects! I want to see the script to create all parts of your database

Note: You may install SQL Server Express on your local system to initially develop & test your code. Your final code must be created in your assigned database using the Anderson VLAB!

Ensure that you regularly visit the “HW3 Discussions” section to verify any additional assumptions or restrictions. 

Required Tasks

The assignment is split into two parts: Implementation & Insertion. Both sections must be complete to successfully complete this assignment.

Part 1 – Implementation (50 points)

Based on the database section described below, you will be responsible to build the database. The following requirements must be done by each student.

1) Create tables defined in the provided database documentation (table descriptions & ERD).

2) Column requirements must be implemented based on the requirements stated.

3) Comments within your stored procedures are welcome.

4) All tables must have a primary key.

5) All tables must be related to at least one other table. No table should stand alone.

6) All tables that have foreign keys must have the appropriate constraint defined and enforced.

7) All tables must have data based on the requirements stated in the Insertion section of the assignment.

8) Use Visual Studio in the VLAB to access your database space on the server. You MUST create and test your statements on the server (review the demo video in Learn).

9) A database stored procedures must be created for your database.

10) Your SQL-DDL code must run on SQL Server. Your table creation code must be created using Visual Studio and must be in a stored procedure. Review the demo videos in Learn. Your table creation stored procedure must be named as the following: HW3CreateTables

11) Foreign Key constraints must be defined in a separate stored procedure, and must exist as an alteration of the tables that were created above. Your stored procedure must be named as the following: HW3CreateRelationships

12) Additional constraints must be defined in a separate stored procedure, and must exist an alteration of the tables that were created above. Your script must be named as the following: HW3CreateConstraints

13) One stored procedure must exist to create all tables, constraints and foreign keys as demonstrated in the lecture videos. Your stored procedure must be named as the following: HW3CreateDatabase

14) Another set of stored procedures must exist to drop tables, constraints and foreign keys. Your stored procedures must be named as the following: HW3DropConstraints, HW3DropRelationships, and HW3DropTables

15) One stored procedure must exist to delete all tables, constraints and foreign keys as demonstrated in the lecture videos. Your stored procedure must be named as the following: HW3DropDatabase

16) Comments identifying the different table levels must exist in your script files. Refer to the online sessions for more clarifications about “table levels”.

17) Column names must NOT have the following: contain spaces, be in all capital letters, or use special characters (e.g. #, /, period, dashes, etc.). It is recommended that you follow the naming schemes provided in the lecture & demo videos. CAMEL case is strong suggested for the naming convention for your columns. Pascal Case is recommended for tables, relationships and constraints.

Part 2 – Insertion (30 points)

Each student is required to insert data to their database. The data must be inserted in the proper order. Refer to the online sessions for more clarifications about “table levels”. Keep in mind that data from one table must be inserted before data in other tables can exist. In addition, data in one database section must be loaded before data in the other section can exist.

1) Your data insertion code must be created using Visual Studio and must be in a set of stored procedures. Review the demo videos in Learn.

2) Each level of tables must be inserted in order. So do not create level 1 tables, before all level 0 tables have been created, etc. Your insertion stored procedure must be named as the following: HW3InsertData

The stored procedure listed above will contain the insert statements needed to add data to the database. A number of rows of data will need to be inserted into your tables and in a particular order. The following describes how much data should exist (at minimum) in your tables.

1) A starting set of data has been provided to you in an Excel document. This data is for Level-0 tables.

2) Based on the Level-0 data, you will need to generate data for all other tables.

a. Level-1 and Level-2 Tables: Follow the directions in the Excel tab for the number of additional rows to create

b. Level-3 (and higher) Tables, if necessary: Follow the directions in the Excel tab for the number of additional rows to create

3) Note that Level-4 tables will be based on Level-3 tables, Level-3 data will be based on Level-2 tables, and so on. Your data will be a mix of the sample data and the data that you create.

Finally, the entire database will be created and related. Deductions will be issued if the stored procedures do not work together to create and clear your database. A set of stored procedures must be built to create the entire database. The set of stored procedures must be named the following:

1) HW3FullCreateDatabase

a. Creates tables, relationships, constraints, and then inserts data

i. This stored procedure will call HW3CreateDatabase and HW3InsertData

2) HW3FullDropDatabase

a. Drops constraints, relationships, and tables

i. This stored procedure will call HW3DropDatabase

It is advised that you refer to lecture videos and online sessions that will review how each stored procedure will be built and be used to create the database.

Extra Credit (5 points)

Using the solution, the Vaccination company wants to track Vaccine Lot numbers. Design the change that will link a LotNumber (required, and auto-generated), and ManufactureDate (required, and must be greater than January 1, 1900) to the Vaccines. Assume that there can be more than one Lot Number for a Vaccine. You must insert at least 3 rows of data into each new table.

All table(s) must be created and related including primary keys and foreign keys. Tables must be prefixed with “EC3”, e.g. EC3Drivers. Create 2 stored procedures (EC3Create, and EC3Clear) that will create the tables, constraints, relationships and insert data in the Create Stored Procedure (one stored procedure to do all of the work), while the Clear stored procedure removes the Extra Credit table(s).

Submission

Since the database will be developed in the VLAB and on a remote server, the code needed to build the database (including data) is not needed as it will be stored on the server. You will need to submit the following in UNM Learn to mark that you are complete:

1) A Microsoft Word, Excel, or Text file that lists the following:

a. List of stored procedures that will build the entire database. The stored procedures must be listed in order of how they should be executed.

b. List of all stored procedures created to insert data into your section of the database.

c. All Extra Credit stored procedures should be listed. If you fail to list your stored procedures for the extra credit, you will NOT earn extra credit points.

2) A backup of each database will be used to grade your assignment. Code is not required as it will be included with the backup of your database. The backup of your database taken at 12:00 on Tuesday, April 6th will be used for grading. If you finish Homework#3 before then, do not modify your HW3 stored procedures until after April 5th.