For this assignment, you will create a relational database schema. Then, you will write SQL scriipts to create the tables in your schema. This schema and SQL scriipt will be used in the Lab 2 assignment to check your SQL scriipt and to practice manipulating data using a SQL playground.
Background
As you have learned throughout this module, SQL is a very valuable tool which is used to create and interact with data in a database. Although SQL is not considered an actual programming language (arguably), you will encounter it more than you may expect when working in the areas of information technology or cybersecurity. This assignment is not intended to make you SQL professionals. But as you have learned, databases are used everywhere and understanding how to interact with them will help you to secure them.
Instructions
The material presented throughout this module will be all you need to complete this assignment. However, feel free to use outside resources to clarify any concepts. The requirements for this assignment are:
1. Create a database schema for the following four tables and include any needed fields for the information listed. As before, create your database schema with any application you are comfortable using. (e.g., Access, Word, PowerPoint, Visio, Draw, etc.) [Note: Consider creating a free account at drawSQL (https://drawsql.app). It is easy to create tables and it allows students to show data types/attributes, primary keys, and relationship lines, etc.]
STUDENT
o Unique student number (length no more than 4)
o Full name of student
o Address (street address, city, state, zip)
o Year enrolled
o Email address
COURSE
o Unique course identifier (e.g., CYB481)
o Name of course
o How many credits
o How many students enrolled
o Faculty ID who is teaching the course
GRADE
o Student ID the grade is for
o Course ID the grade is for
o Grade
FACULTY
o Unique faculty number (length no more than 4)
o Full name of faculty
o Email Address
o Associated Program
o Year Hired
2. Create the table relationships in the schema as follows:
A student can have many grades – A grade can only have one student
A course can have many grades – A grade can only have one course
A faculty can have many courses – A course can only have one faculty
3. Write a SQL scriipt that will create each of the four tables in your schema.