Question

user_id name AppUser birthday owns ISA 7 supervises album_id phone worker name Album Customer Admin supervisor pic creation_date contains places manages image_id order_id Image Print Order filename status amount description contains item_id (product_id IC price quantity Item prints-on Product i subtotal prints

Answer 1

Creating database with name departmental->

CREATE DATABASE departmental;

Using the new database departmental->

USE departmental;

CREATING VARIOUS TABLES->

1. CREATING TABLE AppUser

CREATE TABLE AppUser
(
user_id INT NOT NULL PRIMARY KEY,
name VARCHAR(80) NOT NULL
);

2. Creating table customer

CREATE TABLE customer
(
user_id iNT PRIMARY KEY NOT NULL,
FOREIGN KEY(user_id) REFERENCES AppUser(user_id)
);

3.Creating table customerPhone

CREATE TABLE CustomerPhone
(
user_id INT NOT NULL ,
phone VARCHAR(20),
PRIMARY KEY(user_id,phone)
);

4.Creating table admin

   CREATE TABLE Admin
(
user_id INT PRIMARY KEY NOT NULL,
supervisor_id INT,
FOREIGN KEY(user_id) REFERENCES AppUser(user_id),
FOREIGN KEY(supervisor_id) REFERENCES AppUser(user_id)
);

5.Creating table album

CREATE TABLE Album
(
album_id INT PRIMARY KEY NOT NULL,
creation_date DATE,
user_id INT,
FOREIGN KEY(user_id) REFERENCES AppUser(user_id)
);

6.Creating table image

CREATE TABLE image
(
image_id INT NOT NULL,
album_id INT NOT NULL,
filename VARCHAR(80) NOT NULL,
PRIMARY KEY(image_id,album_id),
FOREIGN KEY(album_id) REFERENCES album(album_id)
);

7.Creating table product

CREATE TABLE Product
(
product_id INT NOT NULL PRIMARY KEY,
description TEXT,
price DECIMAL(10,2)
);

8.Creating table printOrder

CREATE TABLE PrintOrder
(
order_id INT NOT NULL PRIMARY KEY,
user_id INT,
status VARCHAR(80),
pic_id INT,
FOREIGN KEY(user_id) REFERENCES Customer(user_id),
FOREIGN KEY(pic_id) REFERENCES Admin(user_id)
);

9.Creating table Item

CREATE TABLE Item
(
order_id INT NOT NULL,
item_id INT NOT NULL,
quantity INT,
product_id INT,
album_id INT,
image_id INT,
FOREIGN KEY(order_id) REFERENCES PrintOrder(order_id),
FOREIGN KEY(product_id) REFERENCES Product(product_id),
FOREIGN KEY(album_id,image_id) REFERENCES Image(album_id,image_id)
);