Activity #17 CREATE DATABASE WITH CONSTRAINT

Implement foreign and primary key constraints, ensuring the primary key is set to auto-increment. Additionally, insert at least 50 dummy records into each table.

Step 1: Log in to MySQL

1. Open your command prompt or terminal.

2. Log in to your MySQL server using the following command (replace username with your actual MySQL username):

     mysql -u username -p
   

3. Enter your password when prompted.

Step 2: Create a Database

1. Create a new database to hold your tables:

     CREATE DATABASE my_database;
   

2. Select the newly created database:

     USE my_database;
   

   Step 3: Create Tables

   Assuming your ERD has five entities such as QuizDB, EcommerceDB, JobBoardDB, EventManagementDB, and TravelBookingDB, here's how you can create them:

For quizDb:

creating user table:

inserting dummy records:

Using SELECT * FROM user; - - showing record of user

creating quiz table:

inserting dummy records:

Using SELECT * FROM quiz; - - showing record of quiz

creating question table:

inserting dummy records:

Using SELECT * FROM question; - - showing record of questio

creating table options:

inserting dummy records:

Using SELECT * FROM question; - - showing record of option

creating table answer:

inserting dummy records:

Using SELECT * FROM answer; - - showing record of answer

creating table result:

inserting dummy records:

Using SELECT * FROM result; - - showing record of result

2. Creating database EcommerceDb

creating table user:

inserting dummy records:

Using SELECT * FROM user; - - showing record of user

creating table products:

inserting dummy records:

Using SELECT * FROM products; - - showing record of products

creating reviews table:

inserting dummy records:

Using SELECT * FROM reviews; - - showing record of reviews

creating order_items table;

inserting dummy records:

SELECT * FROM order_items;

creating orders table:

inserting dummy records:

Using SELECT * FROM orders; - - showing record of orders

3.CREATE JOBPLATFORMDB

creating user table:

inserting dummy record;

Using SELECT * FROM user; - - showing record of user

creating authentication table:

inserting dummy records;

Using SELECT * FROM authentication; - - showing record of authentication

creating job_posting table:

inserting dummy record;

Using SELECT * FROM job_posting; - - showing record of job_posting

creating job_interaction table:

inserting dummy records;

Using SELECT * FROM job_interaction; - - showing record of job_interaction

creating job_performance table:

inserting dummy data;

Using SELECT * FROM job_performance; - - showing record of job_performance

creating application table:

inserting dummy data;

Using SELECT * FROM application; - - showing record of application

create message table:

inserting dummy data;

Using SELECT * FROM message; - - showing record of message

4. Create EventManagementDb

create admin table:

inserting dummy data;

Using SELECT * FROM admin; - - showing record of admin

create user table:

inserting dummy data;

Using SELECT * FROM user; - - showing record of user

create events table:

inserting dummy data;

Using SELECT * FROM events; - - showing record of events

creating invited table:

inserting dummy data ;

Using SELECT * FROM invited; - - showing record of invited

creating attendees table;

inserting dummy data;

Using SELECT * FROM attendees; - - showing record of attendees

create agenda table:

inserting dummy data;

Using SELECT * FROM agenda; - - showing record of agenda

5.CREATE TravelBookingDb

create user table:

inserting dummy data;

sing SELECT * FROM user; - - showing record of user

create tours table:

inserting dummy data;

Using SELECT * FROM tours; - - showing record of tours

create bookings table:

inserting dummy data

Using SELECT * FROM bookings; - - showing record of bookings

create reviews table:

inserting dummy data;

Using SELECT * FROM reviews; - - showing record of reviews

create payment table:

inserting payment table;

Using SELECT * FROM payment; - - showing record of payment;

create admin table:

inserting dummy data;

Using SELECT * FROM admin; - - showing record of admin;

creating admin_logs table:

inserting dummy data ;

Using SELECT * FROM admin_logs; - - showing record of admin_logs;

SHOWING ALL DATABASE AGAIN:

SHOW TABLES OF QUIZDB;

SHOW TABLES OF ECOMMERCEDB;

SHOW TABLES JOBPLATFORMDB;

SHOW TABLES EventManagementDb;

SHOW TABLES travelbookingDb;