Description
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. 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. 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:
- The database includes 4 tables.
- Sections
- Courses
- Rooms
- Faculty
- Tables input information.
Part II. Scripts and Database Diagram
- Scripts to create for database validation
- 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:
- Write a SELECT statement that joins the Sections table to the Rooms table and returns these columns:
Section#, ID, Instructor
- Sort the result set by ID and then by Instructor in ascending order.
- Write a SELECT statement that returns these columns
- The count of the number of rooms in the Rooms table
- The maximum number of Seats in the Rooms table
- Write a SELECT statement that returns the Section#(s) and ID for the instructor Uddin.
- 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.
- Create a database diagram that shows the relationships between the four tables. (Hint: Expand database > Database Diagram > right-click New Database Diagram …)
