Create a database name "dbXXXXXX" where XXXXXX is the last 6 digits of your BannerID.

Select the database using the "use [database name]" command.

Now you are in the database.

Based on the above schema from Enrolment System database, create all the tables with the last 6 digits of your BannerID as prefix for each table. For example, if the last 6 digits of your BannerID is "123456", then the table name for table Lecturer should be "123456_Lecturer". Refer to chapters 4&5 lecture note to create table that has primary keys and Foreign Keys. Provide the datatype of each attributes.

Add a column called "Department" with datatype "VARCHAR(12)" to the table "Lecturer". Shows the metadata of the updated "Lecturer" table. (Use Describe command)

Drop the "Department" column from the table "Lecturer", and show the metadata of the updated "Lecturer" table.

Insert three (3) data to each of the table in the tables created.

Note: If you have foreign key issues, please disable foreign key constraints before inserting the data, see below

SET FOREIGN_KEY_CHECKS=0;

Fill in the lecturer table using the details of your favorite lecturers.

Fill in the course table using the details of your favorite courses.

Fill in the students table using your own details and two of your course mates.

Fill in the appropriate information in the enrollment table using your data and your course mates data.

Set foreign key constraints back to 1 after you finish inserting your data SET FOREIGN_KEY_CHECKS=1;

Select and display all the data in table course.

Select and display your own data from students table using your student ID.

Update your own year of intake in the students table to 2023.

Delete one lecturer from your lecturer table.

Delete all lecturers from your lecturer table.

Update the information in Figure 1 into your students table using the SQL command you learnt previously.

Figure 1: Table Students.

Perform the operation below:

Retrieve the distinct list of intake year from the table students.

Sort the distinct list of intake year from the table in descending order.

Retrieve a data from table students where the name of the student is Enson Liu and the year of intake is 2016.

Retrieve the data from table students where the name of the student is Enson Liu or the year of intake is 2012.

List all the student in table students that are not from the year 2018.

*Use snipping tools (in windows) or screencapture software to print screen the show database result before and after the task. Save the screencaptures in a Microsoft Document. Save your file as Lab3-YourName.docx and submit on Blackboard