SQL Database Code

For this assignment, I am not providing any data, just a list of expectations/rules that you should base your design on. To complete this assignment, you need to design a database to meet all the below items, then write CREATE TABLE scripts to implement your design. You'll then insert some data and provide a select statement to return it.

This database will be used to help a company manage information about their campgrounds and sites

Rules/Needs

• We manage multiple campgrounds in several states, each with a fun name to help describe it
• Each campground has a manager, and facility engineer, and a host
• We'd like to track each person's role, email address, name, start date and termination date
• Each campground has a number of amenities - things like swimming pools, playgrounds, fresh water, flush toilets, etc.
• Not all campgrounds have the same amenities - we need help tracking what campground has which ones
• Each campground has a number of campsites. For each campsite, we indicate if it is for tents or for trailers. We also track if it is shaded, partially shaded, or sunny. Finally, each campsite is designated a number to help identify it on maps

Some common queries we'll run -

• A list of active employees, their role, and email address
• Campgrounds with specific amenities (i.e. campgrounds with a pool and flush toilets)
• Campsites that are shaded in a given campground

What you need to turn in -

• CREATE TABLE scripts. Again, make sure you have primary key/foreign key relationships defined, put some thought into the correct data type, use good naming conventions, and make sure column nullability is declared. Include DROP TABLE IF EXISTS statements at the top of your script - https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-table-transact-sql?view=sql-server-ver15
• Some insert statements for data you make up. Make sure each of your tables has some information in it (and that it relates properly)
• One of the three common queries described above.