Course Project
Instructions: Design a database that stores information about a registration system. There are four tables in the
“Registration System” database.
• Create a Registration script named i.e. MyRegistration.sql Use what you have learned in the course.
• The database should include four (4) tables with data.
Files to submit: zip file with the files below
• Registration script named i.e. MyRegistration.sql
•
An .sql file that contains all the SELECT statements (single sql with all scripts)
• Screenshot of database diagram (created in SQL Server Management Studio) Part I:
A. The database includes 4 tables.
• Sections
• Courses
• Rooms
• Faculty
B. Tables input information.
Part II. Scripts and Database Diagram
A. Scripts to create for database validation
1. Write SELECT statements to return all column names and data in each table. Sort by the primary key value in ascending order.
Sections table:
Courses table:
Faculty table:
Room table:
2. Write a SELECT statement that joins the Sections table to the Rooms table and returns these columns:
Section#, ID, Instructor
a. Sort the result set by ID and then by Instructor in ascending order.
3. Write a SELECT statement that returns these columns
a. The count of the number of rooms in the Rooms table
b. The maximum number of Seats in the Rooms table
4. Write a SELECT statement that returns the Section#(s) and ID for the instructor Uddin.
5. Write an INSERT statement that adds this row to the Faculty table:
FacultyID:555-55-5555
LName: Smith
FName: John
Address:2100 Southeast Parkway
City: Arlington
State: TX
ZipCode: 76018
Phone: 817-515-5555
5b. Write an UPDATE statement that modifies the Section table for Section# 44324. This statement should change the FacultyID column to 555-55-5555.
5c. Write a DELETE statement that deletes the row you updated in the previous step to the Section table. This statement should use the Section# column and FacultyID column to identify the row.
B. Create a database diagram that shows the relationships between the four tables. (Hint: Expand database > Database Diagram > right-click New Database Diagram …)
