US14-Create Data Definition Language (DDL) for tables

Description:

As a developer, I want to create a DDL for tables to define the data structure for related tables to facilitiate effective communcation between website and database to improve customer interaction with the company.

Acceptance Criteria:

The client reviews and approves DDL for tables through reveiwng DDL statements.

Tasks:

Create a DDL for the website’s database tables. 3hrs - Tobin

CREATE TABLE Membership
 ( MembershipID AUTOINCREMENT NOT NULL PRIMARY KEY,
  Type VARCHAR(10));

CREATE TABLE Category
( CategoryID AUTOINCREMENT NOT NULL PRIMARY KEY,
   CategoryType VARCHAR(20));

CREATE TABLE Class
( ClassID AUTOINCREMENT NOT NULL PRIMARY KEY,
   ClassName VARCHAR(30),
   Description VARCHAR(255),
   Instructor VARCHAR(30),
   Day VARCHAR(10),
   ClassTime VARCHAR(10),
   CategoryID INT,
   FOREIGN KEY (CategoryID)
   REFERENCES Category(CategoryID));

CREATE TABLE Customer
( CustomerID AUTOINCREMENT NOT NULL PRIMARY KEY,
   FirstName VARCHAR(30),
   LastName VARCHAR(30),
   Address VARCHAR(30),
   City VARCHAR(30),
   State VARCHAR(30),
   Zip INT,
   Email VARCHAR(30),
   MembershipID INT,
   FOREIGN KEY (MembershipID)
   REFERENCES Membership(MembershipID));

CREATE TABLE Enrollment
( ClassID INT,
   CustomerID INT,
   CONSTRAINT pk_Enrollment PRIMARY KEY (ClassID, CustomerID),
   FOREIGN KEY (ClassID)
   REFERENCES Class(ClassID),
   FOREIGN KEY (CustomerID)
   REFERENCES Customer(CustomerID));

CREATE TABLE UserInfo
( UserID AUTOINCREMENT NOT NULL PRIMARY KEY,
   UserName VARCHAR(30),
   Password VARCHAR(30),
   FOREIGN KEY (UserID)
   REFERENCES Customer(CustomerID));