Laboratory activity database and table

This is my first laboratory in my class database management system. In this computer laboratory activity, students are going to create their database and table in MySQL command prompt. It is good for the student to start learning SQL commands using the command prompt before introducing them to some other tools or interface that is use to create database like the Apache xampp.

The student s in this laboratory activity will create a computerized teacher schedule, it is a web database application that can store details about the schedule of a teacher such as the room, day, time and the subject taught. The data will be stored in MySQL database, PHP will be used to manipulate the data in database and to perform operations such insert, select, update and delete. HTML will be used to create the graphical interface to be used by user of the system.

The create database sql statement will be used in creating he database, and the create table command will be used to create the database table. In this application, three tables will be created; the following tables are the faculty, room, and schedule. These tables will serve as the basic storage of data about faculty, room, and schedule. Below is the conceptual design of a computerized faculty schedule.
  • faculty(faculty_id, firstname, middlename, lastname)
  • room(room_id, roomname)
  • schedule(index, faculty_id, room_id, day, time, subject_taught )

For the faculty table, the faculty_id will serve as the primary key.  IN the table room the primary key will be the room_id. The schedule tabe will become the intersection table, index will be the primary key. The faculty_id and room_id in the schedule table are the foreign keys and they are used to established a relationship to the faculty and room table. The syntax or command to create database and table in MySQL command line is:

mysql> CREATE DATABASE scheduledb;

mysql> use scheduled;

mysql>CREATE TABLE faculty (
    fid varchar(10),
    fname varchar(20),
    mname varchar(20),
    lname varchar(20),
   PRIMARY KEY (fid)
);

mysql>CREATE TABLE room (
    room_id varchar(10),
    room_name varchar(20),
   PRIMARY KEY (room_id)
);

mysql>CREATE TABLE schedule (
    index int,
    fid varchar(10),
    room_id varchar(10),
   day varchar(20),
   time varchar(20),
   subject_taught(20),
   PRIMARY KEY (index) auto increment

);