Assignment 11 For this assignment you will create a database, tables, add test d
ID: 3919850 • Letter: A
Question
Assignment 11 For this assignment you will create a database, tables, add test data and perform a query Create a new database that holds details for course roster information called CourseManagementDB Create 3 tables: 1. 2. a. Students: here you will have student information i. Student ID ii. First Name iii. Last Name iv. Date Of Birth b. Courses: here course information will be held i. Course ID ii. Course Name iii. Course Department iv. Course Start Date c. Course Roster: here you will link the students with the course i. Course ID ii. Student ID iii. Registration Date d. Make sure to set the proper data types, primary keys for each table and create the proper relationships between the tables 3. 4. Add test records to the tables (add at least 5 test records for each table) Run a SELECT query that uses all three tables using a JOIN, and output the following: a. Student ID, Student Full name, Course Name, Course Start Date, Registration Date Take screen shots of every step, label them and provide some explanation of the screen shots. Save it into a word document and submit to Assignment 11 Dropbox Make sure to complete your work on your VM 5. 6.Explanation / Answer
Create DATABASE CourseManagementDB;
Create Table Student (
StudentId INT ,
FirstName Char(20) NotNull,
LastName Char(20) Not Null,
DateOfBirth DATETIME,
Primary Key (StudentId));
Create Table Course(
CourseId INT ,
CourseName Varchar(20) Not Null,
Department varchar(20) Not Null,
StartDate DateTime,
Primary key (CourseId));
Create Table CourseRoster(
CourseId INT,
StudentId INT,
RegistrationDate DateTime Not Null,
Foreign Key (CourseId) Reference Course (CourseId),
Foreign Key (StudentId) Reference Student (StudentId));
Insert into student values (123,'John','Test','12-06-1990');
Insert into student values (124,'Tom','Jerry','01-01-1945');
Insert into student values (125,'Jonny','Test','04-03-1985');
Insert into student values (126,'Kat','Kid','12-12-1999');
Insert into student values (127,'Boss','Baby','12-12-2016');
Insert into course Values (21,'Java','CS','25-08-2018');
Insert into course Values (11,'c++','CS','25-08-2018');
Insert into course Values (22,'SQL','CS','25-08-2018');
Insert into course Values (23,'Thermodynamics','ME','25-08-2018');
Insert into course Values (24,'Design','ME','25-08-2018');
Insert Into courseroaster values (11,123,'12-07-2018');
Insert Into courseroaster values (24,123
4,'12-07-2018');
Insert Into courseroaster values (22,124,'12-07-2018');
Insert Into courseroaster values (23,125,'12-07-2018');
Insert Into courseroaster values (24,127,'12-07-2018');
Select s.studentid,(s.first-name+' '+s.last name) As FullName,c.coursename,c.startdate,CR.registrationdate from student s inner join courseroster CR on s.studentid=CR.studentid inner join course c on cr.courseid=c.courseid;