Japanese Foodies is one of the premier culinary training schools of Japanese cuisine in Dubai. Customers can
Question:
Japanese Foodies is one of the premier culinary training schools of Japanese cuisine in Dubai. Customers can make reservations on any Japanese cooking class based on their needs. Different classes are listed on their website with prices for each. class reservation must indicate class date, time, price, cuisine type (Dim Sum, Sushi, Ramen), the chef who will train, and lesson status code (completed, cancelled, in progress). Customers details are saved in the DB, such as the date they became customers, DOB, customer status (beginner, advanced), first and last name, amount of fees outstanding, email address, and phone number. Cooking classes payments are recorded separately indicating payment method (credit card, cash), date and time, and amount paid.
question: Detailed code for the implementation of your database showing SQL code to create DB and tables with all necessary keys (Primary & Foreign), code to insert, modify, and delete data into the tables, and a sample SQL code to retrieve data
CREATE DATABASE Japanese_Foodies;
USE Japanese_Foodies;
CREATE TABLE Classes (
Class_ID int NOT NULL AUTO_INCREMENT,
Class_Name varchar(255) NOT NULL,
Class_Date date NOT NULL,
Class_Time time NOT NULL,
Class_Price decimal(5,2) NOT NULL,
Cuisine_Type varchar(255) NOT NULL,
Chef_Name varchar(255) NOT NULL,
Lesson_Status varchar(255) NOT NULL,
PRIMARY KEY (Class_ID)
);
CREATE TABLE Customers (
Customer_ID int NOT NULL AUTO_INCREMENT,
First_Name varchar(255) NOT NULL,
Last_Name varchar(255) NOT NULL,
Date_Joined date NOT NULL,
DOB date NOT NULL,
Customer_Status varchar(255) NOT NULL,
Amount_Fees decimal(5,2) NOT NULL,
Email_Address varchar(255) NOT NULL,
Phone_Number varchar(255) NOT NULL,
PRIMARY KEY (Customer_ID)
);
CREATE TABLE Payments (
Payment_ID int NOT NULL AUTO_INCREMENT,
Payment_Method varchar(255) NOT NULL,
Payment_Date date NOT NULL,
Payment_Time time NOT NULL,
Payment_Amount decimal(5,2) NOT NULL,
PRIMARY KEY (Payment_ID)
);
INSERT INTO Classes (Class_Name, Class_Date, Class_Time, Class_Price, Cuisine_Type, Chef_Name, Lesson_Status)
VALUES ('Dim Sum', '2020-07-15', '10:00', 80.00, 'Dim Sum', 'John', 'in progress');
INSERT INTO Customers (First_Name, Last_Name, Date_Joined, DOB, Customer_Status, Amount_Fees, Email_Address, Phone_Number)
VALUES ('Mary', 'Smith', '2019-03-04', '1975-01-21', 'beginner', 0.00, '..h@g ail.com', '555-123-4567');
INSERT INTO Payments (Payment_Method, Payment_Date, Payment_Time, Payment_Amount)
VALUES ('credit card', '2020-07-15', '11:00', 80.00);
The code creates a database called Japanese_Foodies. Within this database, there are three tables: Classes, Customers, and Payments. The Classes table includes information on each cooking class offered by the Japanese Foodies school. This includes the name and date of the class, the time, price, cuisine type, and the name of the chef who will be teaching the class. There is also a Lesson_Status column to indicate whether a particular class has been completed, cancelled, or is still in progress.
The Customers table includes information on each customer of the Japanese Foodies school. This includes the customer's first and last name, the date they became a customer, their date of birth, customer status (beginner or advanced), and how much money they owe in fees. There is also an email address and phone number for each customer.
The Payments table includes information on each payment made to the Japanese Foodies school. This includes the payment method (credit card or cash), the date and time of the payment, and the amount paid.
The code also inserts three rows of data into each table. The first row inserted into the Classes table is for a Dim Sum class that is taking place on 15th July 2020 at 10am. The second row inserted into the Customers table is for a customer named Mary Smith. The third row inserted into the Payments table is for a payment of 80.00 made by credit card on 15th July 2020 at 11am.
Core Concepts Of Accounting Information Systems
ISBN: 9780470507025
11th Edition
Authors: Nancy A. Bagranoff, Mark G. Simkin, Carolyn Strand Norman