Create and drop a database tables using appropriate data types and indexing

Objectives

Create a MySQL Schema

Create and drop a database tables using appropriate data types and indexing

Describe and create table constraints

Code primary key constraints

Create and drop indexes

Create a backup file of your database

Description

This database will be used by a college to build a class schedule from courses that are available in the curriculum. The database tables need to be built with appropriate fields and relationships defined.

Database tables must be created for the following entities:

Courses (course numbers, course names and description).

Students (Name, email, phone, major).

Faculty (Name, email, phone, office number, department).

Class scheduled (class name, number, description, start and end dates and times, and room number).

Requirements

Read Murach textbook: Chapter 11 and 19.

Review the linked lecture article(s).

Create new MySQL database tables.

Identify the attributes, their data types and lengths for each field in your tables.

Write a script to create the table structures for each of your tables.

Be sure to include all primary key constraints.

Add sample data to each of your tables.

Create a single file table definition using MySQL or the Work Bench.

Add the SHOW TABLE STATUS for all tables at the end of the script.