What is SQL? Name two DBMS that you have came across that can uses SQL?

SQL is a database language designed for managing data held in a relational database management system.

I’ve come across mySQL & Microsoft SQL server

What is the purpose of column constraint? Name the common optional column constraints available.

The purpose of a column constraint is to initialize a value for a new record.

The optional ColumnConstraints are NULL, NOT NULL, UNIQUE, PRIMARY KEY, DEFAULT and AUTO_INCREMENT.

Using the data information provided in the next 3 slides, implement the schema using Transact SQL (show SQL statements for each table). Implement the constraints as well.

  • tblLevels
    • Level – auto increment PK
    • ClassName – text 20 – nulls are not allowed

CREATE TABLE tblLevel
(
Level NOT NULL AUTO_INCREMENT,
ClassName CHAR(20) NOT NULL,
CONSTRAINT tblLevel_PK PRIMARY KEY(Level)
);

  • tblPool
    • Pool – auto increment PK
    • PoolName – text 20 – nulls are not allowed
    • Location – text 30

CREATE TABLE tblPool
(
Pool NOT NULL AUTO_INCREMENT,
PoolName CHAR(20) NOT NULL,
Location CHAR(30) NULL,
CONSTRAINT tblPool_PK PRIMARY KEY(Pool)
);

  • tblStaff
    • StaffID – auto increment PK
    • FirstName – text 20
    • MiddleInitial – text 3
    • LastName – Text 30
    • Suffix – text 3
    • Salaried – bit
    • PayAmount – numeric

CREATE TABLE tblStaff
(
StaffID NOT NULL AUTO_INREMENT,
FirstName CHAR(20) NULL,
MiddleInitial CHAR(3) NULL,
LastName CHAR(30) NULL,
Suffix CHAR(3) NULL,
Salaried BIT NULL,
PayAmount NUMERIC NULL,
CONSTRAINT tblStaff_PK PRIMARY KEY(Staff)
);

  • tblClasses
    • LessonIndex – auto increment PK
    • Level – Integer FK
    • SectionID – Integer
    • Semester – TinyInt
    • Days – text 20
    • Time – datetime (Formatted for time)
    • Pool – Integer FK
    • Instructor – Integer FK
    • Limit – TinyInt
    • Enrolled – TinyInt
    • Price – numeric

CREATE TABLE tblClasses
(
LessonIndex NOT NULL AUTO_INCREMENT,
FOREIGN KEY (Level) REFERENCES tblLevel,
SectionID Int NULL,
Semester TINYINT NULL,
Days CHAR (20) NULL,
Time DateTime NULL,
FOREIGN KEY (Pool) REFERENCES tblPool,
FOREIGN KEY (Instructor) REFERENCES tblStaff,
Limit TINYINT NULL,
Enrolled TINYINT NULL,
Price NUMERIC NULL,
CONSTRAINT tblClasses_PK PRIMARY KEY(Classes)
);

  • tblEnrollment
    • LessonIndex – Integer FK
    • SID – Integer FK (LessonIndex and SID) Primary Key
    • Status – text 30
    • Charged – bit
    • AmountPaid – numeric
    • DateEnrolled – datetime

CREATE TABLE tblEnrollment
(
LessonIndex INT NOT NULL,
SID INT NOT NULL,
Status CHAR(30) NULL,
Charged BIT NULL,
AmountPaid NUMERIC NULL,
DateEnrolled DateTime NULL,
);

  • tblStudents
    • SID – auto increment PK
    • FirstName – text 20
    • Middle Initial – text 3
    • LastName – text 30
    • Suffix – text 3
    • Birthday – datetime
    • LocalStreet – text 30
    • LocalCity – text 20

CREATE TABLE tblStudents
(
SID NOT NULL AUTO_INCREMENT,
FirstName CHAR(20) NULL,
Middle Initial CHAR(3) NULL,
LastName CHAR(30) NULL,
Suffix CHAR(3) NULL,
Birthday DateTime NULL,
LocalStreet CHAR(30) NULL,
LocalCity CHAR(20) NULL
CONSTRAINT tblStudents_PK PRIMARY KEY(Students)
);

Create the table shown here in mySQL Server. Show the statements you used and include a snapshot of the final table structure.