MySQL

Objective:

You are required to address a set of Data Definition Language (DDL) tasks for a Student Management System. Each query focuses on distinct DDL tasks, including the creation of tables, application of constraints, and establishment of relationships between tables.

Requirements:

You will need to complete at least 20 different DDL tasks, covering table creation, primary keys, foreign keys, constraints, and indexes. Each task represents a key part of designing the structure of the Student Management System.

Question 1: Create the SEMESTER Table

Write a DDL SQL statement to create a SEMESTER table with the following requirements:

• semester_id as a primary key with auto-increment functionality.
• semester_name as a string of maximum length 255 characters, not null.
• start_date as a date field, not null.
• end_date as a date field, not null.
• enrollment_status as string with CHECK constraint (open, close), not null.
• created_date as a date field, default it to system date, not null.

Question 2: Create the STUDENT Table

Write a DDL SQL statement to create a STUDENT table with the following requirements:

• student_id as a primary key with auto-increment functionality.
• first_name as a string of maximum length 255 characters, not null.
• last_name as a string of maximum length 255 characters, not null.
• date_of_birth as a date field, not null.
• email as a unique string, not null.
• created_date as a date field, default it to system date, not null.

Question 3: Create the COURSE Table with Constraints

Create a COURSE table with the following specifications:

• course_id as a primary key with auto-increment functionality.
• course_name as a string with a maximum length of 255 characters, not null.
• department_id as a foreign key, not null.
• credits as an integer, not null.
• Add a CHECK constraint to ensure that the credits are between 1 and 6.
• created_date as a date field, default it to system date, not null.

Question 4: Create the ENROLLMENT Table

Define an ENROLLMENT table with the following details:

• enrollment_id as a primary key with auto-increment functionality.
• enrollment_date as a date field, with the default value being the current date.
• student_id as a foreign key referencing the STUDENT table, not null.
• course_id as a foreign key referencing the COURSE table, not null.
• semester_id as a foreign key referencing the SEMESTER table, not null.
• grade_id as a foreign key referencing the GRADE table.
• amount_paid as a decimal (10, 2), null.
• amount_due as a decimal (10, 2), null.

Question 5: Create the TEACHER Table with a Foreign Key

Define a TEACHER table:

• teacher_id as a primary key with auto-increment functionality.
• first_name as a string of maximum length 255 characters, not null.
• last_name as a string of maximum length 255 characters, not null.
• email as a unique string, not null.
• hire_date as a date field, not null.
• department_id as a foreign key, null.
• created_date as a date field, default it to system date, not null.

Question 6: Define the TEACHER_COURSE Table

Write the SQL statement to create a many-to-many relationship between teachers and courses. This will require the TEACHER_COURSE table, which includes:

• teacher_id as a foreign key referencing the TEACHER table, not null.
• course_id as a foreign key referencing the COURSE table, not null.
• Set up a composite primary key using teacher_id and course_id.
• created_date as a date field, default it to system date, not null.

Question 7: Create the DEPARTMENT Table

Create a DEPARTMENT table:

• department_id as a primary key with auto-increment functionality.
• department_name as a string of maximum length 255 characters, not null.
• created_date as a date field, default it to system date, not null.

Question 8: Define the CLASSROOM Table with Relationships

Write the SQL statement to create a CLASSROOM table that includes:

• classroom_id as a primary key with auto-increment functionality.
• room_number as a string of maximum length 50 characters, not null.
• capacity as an integer, not null.
• department_id as a foreign key referencing the DEPARTMENT table, not null.
• created_date as a date field, default it to system date, not null.

Question 9: Define the CLASS_SCHEDULE Table with Relationships

Write the SQL statement to create a CLASS_SCHEDULE table that includes:

• class_schedule_id as a primary key with auto-increment functionality.
• semester_id as a foreign key referencing the SEMESTER table, not null.
• course_id as a foreign key referencing the COURSE table, not null.
• teacher_id as a foreign key referencing the TEACHER table, not null.
• start_date as a date field, not null.
• end_date as a date field, not null.
• start_time as a time field, not null.
• end_time as a time field, not null.
• created_date as a date field, default it to system date, not null.

Question 10: Create the EXAM Table

Define an EXAM table with the following specifications:

• exam_id as a primary key with auto-increment functionality.
• exam_name as a date field, not null.
• created_date as a date field, default it to system date, not null.

Question 11: Create the EXAM_SCHEDULE Table

Define an EXAM_SCHEDULE table with the following specifications:

• exam_schedule_id as a primary key with auto-increment functionality.
• exam_date as a date field, not null.
• exam_id as a foreign key referencing the EXAM table, not null.
• semester_id as a foreign key referencing the SEMESTER table, not null.
• course_id as a foreign key referencing the COURSE table, not null.
• room_id as a foreign key referencing the ROOM table, not null.
• total_marks as an integer, not null.
• start_time as time, not null.
• end_time as time, not null.
• created_date as a date field, default it to system date, not null.

Question 12: Create the GRADE Table

Write a DDL SQL statement to create a GRADE table with the following details:

• grade_id as the primary key with auto-increment functionality.
• grade_letter as a string of length 1, not null.
• grade_description as a string of maximum length 255 characters.
• start_percent as a decimal, not null.
• end_percent as a decimal, not null.
• Add a CHECK constraint to ensure the grade_letter is between 'A' and 'F'.
• created_date as a date field, default it to system date, not null.

Question 13: Create the EXAM_RESULT Table

Define an EXAM_RESULT table with the following specifications:

• exam_schedule_id as a foreign key referencing the EXAM_SCHEDULE table, not null.
• student_id as a foreign key referencing the STUDENT table, not null.
• grade_id as a foreign key referencing the GRADE table, not null.
• created_date as a date field, default it to system date, not null.
• create composite primary key.

Question 14: Create the EXAM_RESULT_MASTER Table

Define an EXAM_RESULT table with the following specifications:

• exam_id as a foreign key referencing the EXAM table, not null.
• student_id as a foreign key referencing the STUDENT table, not null.
• grade_id as a foreign key referencing the GRADE table, not null.
• semester_id as a foreign key referencing the SEMESTER table, not null.
• created_date as a date field, default it to system date, not null.
• create composite primary key.

Question 15: Create the ATTENDANCE Table

Write a DDL SQL statement to create an ATTENDANCE table:

• attendance_id as the primary key with auto-increment functionality.
• attendance_date as a date field, not null.
• student_id as a foreign key referencing the STUDENT table, not null.
• course_id as a foreign key referencing the COURSE table, not null.
• absence_flag as a boolean, true or false, not null.
• created_date as a date field, default it to system date, not null.

Question 16: Create the FEE_STRUCTURE Table with Constraints

Write a DDL SQL statement to create a FEE_STRUCTURE table:

• fee_id as a primary key with auto-increment functionality.
• course_id as a foreign key referencing the COURSE table, not null.
• fee_amount as a decimal (10, 2), not null.
• Add a CHECK constraint to ensure the fee_amount is greater than 0.
• created_date as a date field, default it to system date, not null.

Question 17: Create the FEE_PAYMENT Table with Constraints

Write a DDL SQL statement to create a FEE_PAYMENT table:

• payment_id as a primary key with auto-increment functionality.
• student_id as a foreign key referencing the STUDENT table, not null.
• amount_paid as a decimal (10, 2), not null.
• balance_amount as a decimal (10, 2), not null.
• payment_date as a datetime, not null.
• Add a CHECK constraint to ensure the fee_amount is greater than 0.

Question 18: Create the EMPLOYEE_LOGIN Table

Write a DDL SQL statement to create an EMPLOYEE_LOGIN table (one-to-one relationship with employee table):

• employee_id as a foreign key referencing the EMPLOYEE table.
• login_id can use alpha numeric login id or email as login id, not null.
• password must encrypt the password before storing it in the database table, not null.
• active_flag use numeric, 0 or 1, you can lock the customer login when required, not null.
• last_login_datetime as a timestamp, last successfull login date for a given employee.

Question 19: Create the AUDIT_LOG Table

Define an AUDIT_LOG table to track changes in the student management system:

• log_id as a primary key with auto-increment functionality.
• log_date as a date field, not null.
• action as a string to describe the action performed, not null.
• user_id as a foreign key referencing the TEACHER or STUDENT table.

Question 20: Add a Column to the TEACHER Table

Write a SQL statement to add a phone_number column of type VARCHAR(15) to the TEACHER table.

Question 21: Alter a Column in the STUDENT Table

Write a SQL statement to alter the email column in the STUDENT table, increasing its length to 255 characters.

Question 22: Drop an Index from the TEACHER Table

Write a SQL statement to drop the index on the phone_number column in the TEACHER table.

Question 23: Rename the FEE_PAYMENT Table

Write a SQL statement to rename the FEE_PAYMENT table to PAYMENT_TRANSACTION.

Question 24: Add Primary Key

Write a SQL statement to add primary key to the EMPLOYEE_LOGIN table.

Question 25: Add a Foreign Key column to the EXAM_SCHEDULE Table

Add a foreign key in the EXAM_SCHEDULE table to reference the CLASSROOM table.

Question 26: Drop a Column from the EXAM_SCHEDULE Table

Write a SQL statement to drop the class_id column from the EXAM_SCHEDULE table.

Question 27: Add Indexes to the STUDENT and COURSE Tables

Write SQL statements to:

• Add an index on the last_name column in the STUDENT table.
• Add an index on the course_name column in the COURSE table.

Question 28: Add a CHECK Constraint on the ENROLLMENT Table

Write a SQL statement to add a CHECK constraint to the ENROLLMENT table ensuring that the grade is between 'A' and 'F', or NULL.

Question 29: Drop the EMPLOYEE_LOGIN Table

Write a SQL statement to drop the EMPLOYEE_LOGIN table.

Question 30: Add a UNIQUE Constraint on the EXAM Table

Write a SQL statement to add a UNIQUE constraint to the EXAM table ensuring that the exam_name is not repeated.

Question 31: Enforce UNIQUE constraints on all applicable tables.

Apply UNIQUE constraints to columns across the entire database wherever duplicate data is not permitted.

GOOD LUCK WITH YOUR ASSIGNMENT!!!

Don't forget to contact us if you need any further assistance with your assignments, and most importantly, for a manual review and approval of your work.

SAMPLE TABLE DESIGN

To gain complete access, login with gmail or outlook, no need of signup, click here