Read more comic bookstore database Submitted by: Jose Pires Submitted to: Dr. Shengli Yuan CS 4318 – Database Management System University of Houston-Downtown Purpose of Document This is a...

1 answer below »
Read more comic
bookstore database
Submitted by:
Jose Pires
Submitted to:
Dr. Shengli Yuan
CS 4318 – Database Management System University of
Houston-Downtown
Purpose of Document

This is a requirements specification document for a database of comic book stores.

Abstract:
We plan to create a database that employees of the store can use to look through their current
inventory to a comic, apparel, rented comic, renter status, and mangas and determine if they
have any at their location. If there is any, it will be able to tell the employees how many issues
are currently there, rented, or bought recently. Additionally, there will be functions to add and
remove comics to help with new shipments and purchases. Each branch has a min of 2 staff,
and a min of 1 for each type of inventory (apparel, comics, manga, rentable_comics, snacks,
and action figures).
Mission statement

The purpose of the XYZ database system is to maintain the inventory of the comic book chain
and facilitate shipments between branches as well as from publishers and other producers of
merchandise that is sold at the chains. As well as help organize and maintain staff and branch
data.

Mission objective
● To maintain(enter update and delete) branch data.
● To maintain (enter update and delete staff data.
● To maintain(enter update and delete) data of comics at each branch.
● To maintain(enter update and delete) data of action figures at each branch.
● To maintain(enter update and delete) data of apparel at each branch.
● To maintain(enter update and delete) data on rented comics.
● To maintain (enter update and delete) renter status data.
● To maintain(enter update and delete) data on snacks.
● To maintain(enter update and delete) data on mangas.

● To perform searches on branches.
● To perform searches on staff data.
● To perform searches on the inventory of comics at each branch.
● To perform searches on the inventory of action figures at each branch.
● To perform searches on the inventory of apparel at each branch.
● To perform searches on rented comics.
● To perform searches on renter status.
● To Perform searches on snacks.
● To Perform searches on mangas.




District
manage
r
Store manager
All
branches
Maintain
Search

x
x


x

Their
branch
Maintain
Search
print




x
All staff Maintain
Search


x


x

their staff Maintain
Search




x
x



All comics
Maintain
Search


X


x

Their
comics
Maintain
Search




X
X

All branch
action
figures
Maintain
Search


X


X

their
branch
action
figures
Maintain
Search




X
X




USE CASES

1. Name: Searches all branches
Actor/Users: District Manager, Store manager
steps:
a.The user requests a search of branches with highest rating
All branch
apparel
Maintain
Search


X


X

Their
branch
apparel
Maintain
Search




X
X

All
Rentable_
Comics
Maintain
Search


X


X

Their
rented
comics
Maintain
Search




X
X

All renter
status
Maintain
Search


X


X

Their
renter
status
Maintain
Search




X
X

All mangas Maintain
Search


X


X

Their
mangas
Maintain
Search




X
X

All snacks Maintain
Search


X


X

Their
snacks
Maintain
Search

X
X

b. The system prints the results including branch_id, address, rating and telephone#
are printed on the screen and the user is asked if they would
like to make another search, exit the program, or complete another task.
c. The user is sent to the appropriate menu accordingly

SELECT B.Branch_id, Address, Name, Date, IssueNumber, Telephone#, price
FROM Branch B, Rentable_Comics R
WHERE B.Branch_id = R.Branch_id;




2. Name: Searches all staff
Actor/User District manager, Store manager
steps:
a.The user requests a search for new staff.
b.The system prompts the user to enter their search criteria, Staff Id, Name,
Position, or Branch_id
c.The system prints the results including staff_id, name, and position are printed on
the screen, and the user is asked if they would like to make another search, exit
the program, or complete another task.
d.The user is sent to the appropriate menu accordingly.


SELECT S.Staff_id, name, position, Telephonephone#
FROM Staff S, Branch B
WHERE S.Branch_id = B.Branch_id;



3. Name: Enter Their staff
Actor/User: store Manager
Steps:
a.The user requests to Enter a new staff
b. The system asks for the user's new staff name and position. Then
assigns a Staff id and branch_id.
c. The system Displays staff_id, name, position, and branch_id on the screen
and asks for
Confirmation.
d. If correct the system creates the new entry and asks if the user would
like to center maintain the staff, exit the program, or complete
another task.
e.The user is sent the appropriate menu.

INSERT INTO staff
VALUES ( ‘S001’, ‘John Smith’. ‘Store Manager’ ,’B002’);

4.Name: Delete Their staff
Actor/User: store Manager
Steps:
a,.The user requests to Delete a staff is chosen
b.The system asks the user to enter the staff_id of the user to be deleted.
c.The system displays the staff_id, name, and position of the staff that has been
selected and asks the
user to confirm the selection.If correct the system deletes the user from
3.The user is sent the appropriate menu.

DELETE FROM staff
WHERE staff_id=’B003’;


5.Name: Update Their staff
Actor/User: store Manager
Steps:
a.The user requests to Updated information is chosen.
b.The system asks the user to enter the staff_id of the user they want to update.
c.The system asks what attribute you want to update, name, position
e.The system asks for the updated data.
f.The system updates the table and asks if you want to maintain the staff, exit the
program, or complete another task.
g.The user is sent the appropriate menu.

UPDATE staff
SET name=’Bob Smith’
WHERE Staff_id = “S0343’’;

6. Name: Search their staff
Actor/User Store manager
steps:
a.The user requests a search for their staff for the highest rating
c.The system prints the results including COUNT(staff_id), and Branch_ID are printed on
the screen and the user is asked if they would
like to make another search, exit the program, or complete another task.
d. Users are sent to the appropriate menu.


SELECT branch_id, COUNT(staff_id) AS count staff
FROM staff
GROUP BY branch_id



7. Name: Search all comics
Actor/User: District manager, Store manager
steps:
a. The user requests a search of all comics by branch_id.
c. The system asks for the search criteria.
d. The system displayed the sku name issunumber and telephone_number, address
the console and asks the user if they would like to search again, exit the program, or
complete another task.
e. the user is sent to the appropriate menu.


SELECT C.sku, name, IssueNumber, Telephonephone#, address
FROM Comics C, Branch B
WHERE C.Branch_id = B.Branch_id;



8. Name: Enter their comics
Actor/user: Store manager
Steps:
The user requests to enter a comic.
a.The system asks the user for the name,IssueNumber, date, price,
stock, and Generates a sku#, as well as a branch_id.
b.The system Displays all sku#, name, IssueNumber, date, price, stock,
and branch_id on the screen and asks for confirmation.
c.If correct, the system creates the new entry and asks the user if they would like
to enter a new comic, exit the program, or complete another task.
d.The user is sent the appropriate menu.

INSERT INTO Comics
VALUES ( ‘12345’, ‘Superman’, ‘10’, ‘1/11/2001’, 8.50, 3, ’B002’);

9. Name: Delete their comics
Actor/user: Store manager
Steps:
a. User request to Delete a comic
b.The system asks a user to enter the sku# of the comic to be deleted.
c.The system displayed sku#, name, IssueNumber, date, price, and stock of the
comic that has been selected and asks
The user To confirm the selection.
d. If correct, the system deletes the comic FROM the table and asks if you would
like to maintain the comics, exit the program, or complete another task.
e.The user is sent to the appropriate menu.

DELETE FROM Comic
WHERE sku=’12345’;


10. Name: update their comics
Actor/user: Store manager
Steps:
a.User request to update a comic
b.the system asks the user to enter the sku# of the comic they want to update.
c. The system asks if the user wants to update The name, date, or IssueNumber,
or the stock.
d. The system asks for the updated data.
d. the system displayed the sku#, name, date, IssueNumber, price, stock, and
asked if it was correct.
e. If correct The system updates the table and asks if you want to maintain the
comics, exit The program or complete another task.
f. The user is sent the appropriate menu.

UPDATE Comics
SET name=”SUPERMAN’’
WHERE SKU = XXXXXXXXXX’;



11. Name: Search their comics
Actor/User: Store manager
steps:
a. The user requests a search of all comics for Max stock For each Branch and
Sku
c. The system asks for the search criteria.
d. The system displays the sku#,branch_id , and max stock in
the console and asks the user if they
would like to search again, exit the program, or complete another task.
e. the user is sent to the appropriate menu.

SELECT Sku, branch_id, Max(stock) AS Max Stock
FROM Comics
GROUP BY Sku, Branch_id








12.Name: Search all action_figures
Actor/User: District manager , Store manager steps:
a. The user requests a search of all action figures by branch id
b. The system asks for the search criteria
c. The system displays the sku#, name, price, stock, and branch_id in the
XXXXXXXXXXConsole and asks the user if they
would Like to search again, exit the program, or complete another task.
d.The user is sent the appropriate menu.


SELECT AC.sku, name ,price, stock, Telephonephone#, address
FROM Comics AC, Branch B
WHERE AC.Branch_id = B.Branch_id;




13. Name: Enter their Action_figures
Actor/user: Store manager
Steps:
a. The user requests to enter an action figure.
b.The system asks the user for the name, stock and Generates an
sku#, and branch_id.
c.The system Displays sku#, name, price, stock and branch_id on the screen and
asks for Confirmation.
d.If correct, the system creates the new entry and asks if you would like to
maintain another action figure, exit the program, or complete another
task.
e.The user is sent the appropriate menu.

INSERT INTO Action_Figures
VALUES ( ‘12345’, “superman toy”,1, 2.53,’B006’ );

14. Name: Delete their Action_Figures
Actor/user: Store manager
Steps:
a.The user requests to delete an action figure
b.The system asks the user to enter the sku# of the action figure to be deleted.
c.The system displays the sku#, name, price, stock and branch_id of the action
XXXXXXXXXXfigure that has been selected
and asks the user To confirm the selection.
d.If correct, the system deletes the action figure and asks if you would maintain
another action figure, exit the program, or complete another task.
e.The user is sent the appropriate menu.

DELETE FROM Action_Figures
WHERE sku=’12345’;

15. Name: Update their Action_Figures
Actor/user: Store manager
Steps:
a. The user requests an update Action_Figure.
b.The system asks the user to enter the sku# of the action figure they want to
Update.
c.The system asks for the updated data.
d.The system displayed the sku#, name, price, stock and branch_id and asked if
it was correct.
e.If correct The system updates the table and asks if you want to maintain their
Action_Figures, exit The program, or complete another task.
f.The user is sent the appropriate menu.

UPDATE Action_Figures
SET Stock=6
WHERE SKU = ’13482’;


176 Name: Search their action figures
Actor/User: Store manager
steps:
a. The user requests a search of their action figures by max stock
b. The system displays the sku#, Branch_id, max stock in the console and
asks the users if they would like to search again, exit the program, or
complete another task.
c. The user is sent to the correct menu.

SELECT Sku, Branch_id, Max(stock) AS Max Stock
FROM Comics
GROUP BY Sku, Branch_id



17. Name: Search all apparel
Actor/User: District manager, Store manager
steps:
a. The user requests a search of all apparel by branch id.
b. The system asks for the search criteria.
c. The system displayed the sku#, hoodie, shirt, price, stock in the console and
asks the users if they would like to search again, exit the program, or
complete another shirt.
d. The user is sent to the correct menu.

SELECT Sku, Hoodie, Shirt, Price, Telephone#, Address, A.Branch_id, Stock
FROM Apparel A, Branch B
WHERE A.Branch_id = B.Branch_id;



18. Name: Enter their apparel
Actor/user: Store manager
Steps:
a. The user requests to Enter action figures.
b.The system asks the user for the name, hoodie, shirt, and Generates an SKU
and branch_id.
c.The system Displays sku#, name, hoodie, shirt, price, stock, and branch_id
on the screen and asks for confirmation.
d. If correct, the system creates a new entry and asks the user if they would like
to enter their apparel, exit the program, or complete another task.
e. The user is sent the appropriate menu.

INSERT INTO Apparel
VALUES ( ‘12345’, ‘TRUE’, ‘Shirt’, XXXXXXXXXX, 5, ’B002’);

19. Name: Delete their apparel
Actor/user: Store manager
Steps:
a. The user requests to Delete apparel.
b.The system asks the user to enter the sku# of the apparel to be deleted.
c.The system displays the sku#, hoodie, or shirt,
of the apparel that has been selected and asks
the user To confirm the selection.
d.If correct the system deletes the apparel FROM the staff table and asks would
Like to enter apparel, exit the program, or complete another task.
e.The user is sent the appropriate menu.

DELETE FROM Apparel
WHERE sku=’12345’;


20. Name: Update their apparel
Actor/user: Store manager
Steps:
a. The user requests to Update apparel
b.The system asks the user to enter the sku# of the action figure they want to
update.
c.The system asks if the user wants to update the hoodie, shirt, or stock.
d.The system asks for the updated data.
e.The system displays the sku, hoodie, shirt, price, stock and asks if it is correct.
f.If correct The system updates the table and asks if you want to maintain
apparel, exit The program, or complete another task.
g.The user is sent the appropriate menu.



UPDATE Apparel
SET price= XXXXXXXXXX
WHERE Sku =’13433’;

21. Name: Search their apparel
Actor/User: store manage
steps:
a. The user requests a search of their apparel.
b. The system asks the user if they want to search by sku#, name, hoodie, or
shirt.
c. The system asks for the search criteria.
d. The system displays the sku, and MAX(stock) in the console and
asks the users if they would like to search again, exit the program, or
complete another task.
e. The user is sent to the correct menu.

SELECT Sku, Branch_id, MAX(Stock) AS Max Stock
FROM apparel
GROUP BY branch_id, Sku


22. Name: Search all rented comics
Actor/User: District manager, Store manager
steps:
a. The user requests a search of all comics.
b. The system asks the user if they want to search by sku#, name, release_date,
IssueNumber, or branch_id.
c. The system asks for the search criteria.
d. The system displayed the search results in the console and asks the users if
they would like to search again, exit the program, or complete another
Task.
E. the system sends the user to the appropriate menu

SELECT R.sku, name, IssueNumber, date, price, R.Branch_id, renting_id
Telephone#, address
FROM Rentable_Comics R, Branch B
WHERE R.Branch_id = B.Branch_id;


23. Name: Enter rented comics
Actor/user: Store manager
Steps:
a. user requests to Enter a rented comic.
b.The system asks the user for the name, IssueNumber. And Generates a sku#,
and branch_ID.
c.The system Displays sku, name, IssueNumber, date, price, branch_id, and
renting_id on the screen and asks for confirmation.
d. If correct, the system creates the new entry and asks the user if they would
like to Enter Rentable_Comics, exit the program, or complete another
task.
e.The user is sent the appropriate menu.

INSERT INTO Rentable_Comics
VALUES ( ‘12345’, ‘Superman’, 10, 1/11/2001’, 8.50, ’B0002’, ‘R0002);

24. Name: Delete rented comics
Actor/user: Store manager
Steps:
a. user requests to Delete rented comic
b.The system asks the user to enter the sku# of the rentable comic to be deleted.
c.The system displays the data of the rentable comic that has been selected and
asks the user To confirm the selection.
d.If correct, the system deletes the rentable comic and asks the users if they
would like to delete comics, exit the program, or complete another task.
e.The user is sent the appropriate menu.

DELETE FROM Rentable_Comics
WHERE sku=’13445’;



25. Name: Update rented comics
Actor/user: Store manager
Steps:
a. user requests to Update rented comic
b.The system asks the user to enter the sku# of the rentable comic they want to
Update.
c.The system asks if the user wants to update the name or IssueNumber.
d. The system asks for the updated data.
e.The system displays the sku, name, date, IssueNumber, renting price and asks
if it is correct.
f.If correct The system updates the table and asks the users if they want to
Update apparel, exit The program, or complete another task.
g.The user is sent the appropriate menu.

UPDATE Rented Comic
SET name=”superman”
WHERE name = “Batman and Superman’’;

26. Name: Search their rented comics
Actor/User: Store manager
steps:
a. The user requests a search of all comics.
b. The system asks the user if they want to search by max Stock
c. The system asks for the search criteria.
d. The system displays the sku, name, IssueNumber, date, price in the
console and asks the users if they would like to search again, exit the
program, or complete another task.
e. the user is sent to the appropriate menu.

SELECT Sku, Branch_id, Max(Stock) AS Max Stock
FROM Rentable_Comics
GROUP BY Sku, ranch_id



27. Name: Search Renter_Status
Actor/user District manager , store manager
steps:
a.The user requests a search of all renter status by brach id.
b.The system asks for search criteria.
c. The system displays to consol the renting_id, comic, name, fees, Name,
rent date, and due date and asks the users if they
would like to search again, exit the program, or complete another task.
d. the user is sent to the appropriate menu.

SELECT RE.renting_id, rentername, IssueNumber, fees, rentday, duedate,
IssueNumber, Date, Price
FROM Renter RE, Rentable_Comics R
WHERE RE.Renting_ID = R.Renting_ID;




28. Name: Enter New Renter
Actor/user: Store manager
Steps:
a.The user requests to Enter Renter.
b.system asks for the user for the Name, rent day,
and due date and generates a renting_id.
c. The system Displays renting_id, fees, Name,
rentdate, and duedate on the screen and asks for confirmation.
d. if correct, the system creates the new entry and asks the user if they would
like. to Enter renter status, exit the program, or complete another task.
e.The user is sent the appropriate menu.

INSERT INTO Renter
VALUES ( ‘R002’, 2.00, ‘John Smith’, ‘1/12/2001’, ‘1/31/2001);



29. Name: Delete their renter status
Actor/user: Store manager
Steps:
a.The user requests to Delete renter status.
bThe system asks the user to enter the renter_id of the renter status they want to
delete.
d.The system displays the renting_id, comic, name, fees, Name,
rent date, and due date and asks if this is the correct id to dellete.
e.If correct The system deletes the table and asks the users if they want to
Delete renter status, exit The program, or complete another task.
f.The user is sent the appropriate menu.

DELETE FROM Renter
WHERE Renter_id=’R0002’;

30. Name: Update Renter
Actor/user: Store manager
Steps:
a. user requests to Update renter status
b.The system asks the user to enter the
c.The system asks if the user wants to update the
d. The system asks for the updated data.
e.The system displays the renting_id, comic, name, fees, Name,
rent date, and due date and asks if it is correct.
f.If correct The system updates the table and asks the users if they want to
Update their renter status, exit The program, or complete another task.
g.The user is sent the appropriate menu.

UPDATE Renter
SET name=’Reed Cook’
WHERE Renter_Id = ’R7483’;

31. Name: Search Renter
Actor/user: Store manager
steps:
a.The user requests a search of their renter status by MAX(Fees_.
b.The system asks the user if they want to search by Renting_Id, Comic Name,
Name, or RentDay.
c.The system asks for search criteria.
d.The system displays to consol the renting_id, Name, and max fees
and asks the users if they
would like to search again, exit the program, or complete another task.
e.The user is sent to the appropriate menu.

SELECT Renting_id, Name, Max(fees) AS Max Fees
FROM Renter
GROUP BY Renting_id, Name

32. Name: Search all Mangas
Actor/user: District Manager, Store manager
Steps:
a.the user requests a search of all renter status By branch_id.
b.The system asks for search criteria.
c. The system displays the sku, name, volume, price, stock, branch_id to the
console and asks the users if they would like to search again, exit the
program, or complete another Task.
d. The user is sent to the appropriate menu.

SELECT M.sku, name, volume,price,stock, M.Branch_id Telephone#, address
FROM Mangas M, Branch B
WHERE M.Branch_id = B.Branch_id;


33. Name: Enter their manga
Actor/user: Store manager
Steps:
a. The user requests to Enter their maga.
b.The system asks the user for a name, volume, price, stock, and Generates a
sku#, and branch id.
c.The system Displays sku, name, volume, price, stock, and branch_id on the
screen and asks for confirmation.
d.If correct, the system creates the new entry and asks the user if they would like
to Enter their manga, exit the program, or complete another task.
e.The user is sent the appropriate menu.

INSERT INTO Manga
VALUES ( ‘14345’, ‘DragonBall’, 10, XXXXXXXXXX, 4, ’B002’);


34. Name: Delete their manga
Actor/user: Store manager
Steps:
a. The user requests to Delete their maga.
b.The system asks the user to enter the sku# of the manga to be deleted.
c.The system displayed the sku, name, volume, price, stock, of the manga that
has been selected and
asks the user To confirm the selection.
d.If correct the system deletes the manga and asks the users if would Like to
Delete manga, exit the program, or complete another task.
e.The user is sent the appropriate menu.

DELETE FROM Manga
WHERE sku=’12745’;

35. Name: Update their manga
Actor/user: Store manager
Steps:
a. The user requests to Update their maga.
b.The system asks the user to enter the sku# of the manga they want to update.
c.The system asks if the user wants to update the name, volume, or stock.
d.The system asks for the updated data.
e.The system displays the sku, name, volume, price, stock, and asks if it is
correct.
f.If correct The system updates the table and asks the users if they want to
Update their manga, exit The program, or complete another task.
g.The user is sent the appropriate menu.

UPDATE Manga
SET name=’One Piece’’
WHERE Sku= ’M4324’;



36. Name: Search their manga
Actor/user: Store manager
Steps:
a.The user requests a search of their manga.
b. The system asks the user if they want to search by name, volume, stock, or
sku#
c.The system asks for search criteria.
d.The system displays the sku, branch_id, and max stock to the
console and asks the users if
they would like to search again, exit the program, or complete another task.
e. the user is sent to the appropriate menu.
`
SELECT Sku, Branch_id, Max(stock) AS Max Stock
FROM Manga
GROUP BY, Sku, Branch_id



37. Name: Search all snacks
XXXXXXXXXXActor/User: District manager, Store manager
steps:
a. The user requests a search for all snacks.
b. The system asks the user if they want to search by sku#, name, or Branch_id.
c. The system asks for the search criteria.
d. The system displays the sku, name, price, stock, branch_id in the console and
asks the user if they
would like to search again, exit the program, or complete another task.
e.The user is sent the appropriate menu.

` SELECT M.sku, Name, Price, Stock, S.Branch_id Telephone#, Address
FROM Snacks S, Branch B
WHERE S.Branch_id = B.Branch_id;

38. Name: Enter their Snacks
Actor/user: Store manager
Steps:
a.The user requests to Enter their snacks
b.The system asks the user for a name, price, stock and Generates a sku# and
banch_id.
c.The system Displays sku, name, price, stock, and branch_id on the screen and
asks for confirmation.
d.If correct, the system creates a new entry and asks the user if they would like
to Enter their snacks, exit the program, or complete another task.
e..The user is sent the appropriate menu.

INSERT INTO Snack
VALUES ( ‘12235’, ‘Cheetos’, 1.50, 20 ,’B0002’);


39. Name:Delete their snacks
Actor/user: Store manager
Steps:
a.The user requests to Delete their snacks
b.The system asks the user to enter the sku# of the snack to be deleted.
c.The system displays sku, name, price, stock, of the snack that has been
selected and asks the user To confirm the selection.
d.If correct, the system deletes the snack and asks the users if they would like to
Delete another snack, exit the program, or complete another task.
e.The user is sent the appropriate menu.

DELETE FROM Snacks
WHERE sku=’12334’;




40. Name: Update their Snacks
Actor/user: Store manager
Steps:
a.The user requests to Update renter Snack
b.The system asks the user to enter Sku of the snack
c.The system asks if the user wants to update the snack
d.The system displays the name of the snack, the price, the purchase date, and
asks if it is correct.
e.If correct The system updates the table and asks the users if they want to
Update their snack, exit The program, or complete another task.
f.The user is sent the appropriate menu.

UPDATE Snacks
SET name=’Skittles’
WHERE Sku= S7482;




41. Name: Search their snacks.
XXXXXXXXXXActor/user: Store manager
XXXXXXXXXXsteps:
a.The user requests a search for their snacks.
b.The system asks the user if they want to search by sku# or name.
c.The system asks for search criteria.
d.The system displays the sku, name, price, stock, to the console and asks the
users if they would like to search again, exit the program, or complete
another task.
e.The user is sent to the appropriate menu.

SELECT Sku, Branch_id, Max(stock) AS Max Stock
FROM Snack
GROUP BY SKU, Branch_id,





42. Name: Enter a branch
Actor/user: District Manager
Steps:
a.The user requests to Enter a new branch
b.The system asks the user for a Branch_id address, telephone#, and rating,
c.The system Displays the Branch_id address, telephone#, and rating,on the
screen and asks for confirmation.
d.If correct, the system creates a new entry and asks the user if they would like
to Enter their branch, exit the program, or complete another task.
e..The user is sent the appropriate menu.

INSERT INTO Their Branch
VALUES ( “B003”, XXXXXXXXXXorchard cookie XXXXXXXXXXHouston Tx’, ‘ XXXXXXXXXX’, 5);


43. Name:Delete a branch
Actor/user: District Manager
Steps:
a.The user requests to Delete their branch
b.The system asks the user to enter the Branch_id of the branch to be deleted.
c.The system displays Branch_id address, telephone#, and rating of the
selected branch and asks the user To confirm the selection.
d.If correct, the system deletes the branch and asks the users if they would like
to Delete another branch, exit the program, or complete another task.
e.The user is sent the appropriate menu.

DELETE FROM branch
WHERE sku=’B0006’;

44. Name: Update a Branch
Actor/user: District Manager
Steps:
a.The user requests to Update a branch
b.The system asks the user the Branch_id of the branch they want to update
c.The system displays the Branch_id address, telephone#, and rating of the
selected branch and asks if it is correct.
e.If correct The system updates the table and asks the users if they want to
Update another snack, exit The program, or complete another task.
f.The user is sent the appropriate menu.

UPDATE Branch
SET telephone# =’ XXXXXXXXXX’
WHERE branch_id = ’B006’;













E-R Diagram


RELATION MODEL


















//Branch
8888
CREATE ASSERTION RRange
CHECK(NOT EXIST)(SELECT Rating
FROM Branch where Rateing >5 || Rateing<0);

CREATE TABLE Branch (
Branch_ID CHAR(5) NOT NULL,
Address VARCHAR XXXXXXXXXXNOT NULL Unique,
Telephone# VARCHAR(10) NOT NULL Unique,
Rating Int(1) NOT NULL,
CONSTRAINT RRange
PRIMARY KEY(Branch_ID);



//Snack


CREATE ASSERTION SStock
CHECK(not exist)(SELECT Stock
FROM Snacks
WHERE Stock > 20)

CREATE ASSERTION SPrice
CHECK (not exist)(SELECT Price
FROM Snacks
WHERE Price< 0)


CREATE TABLE Snacks (
SKU VARCHAR XXXXXXXXXXNOT NULL,
Name VARCHAR(30) NOT NULL,
Price DOUBLE PRECISION XXXXXXXXXXNOT NULL
CONSTRAINT SPrice,
Stock Integer(2) NOT NULL DEFAULT 0
CONSTRAINT SStock,
Branch _id CHAR(5) NOT NULL,
PRIMARY KEY (SnacksSku),
FOREIGN KEY(Branch_id) REFERENCES Branch) ON UPDATE CASCADE;


//Action Figures

CREATE ASSERTION AFStock
CHECK(not exist)(SELECT Stock
FROM ActionFigure
WHERE Stock > 20)

CREATE ASSERTION AFPrice
CHECK(not exist)(SELECT Price
FROM ActionFigure
WHERE Price< 0)


CREATE TABLE Action_Figures (
Sku VARCHAR XXXXXXXXXXNOT NULL,
Name VARCHAR(30) NOT NULL,
Price DOUBLE PRECISION XXXXXXXXXXNOT NULL
CONSTRAINT AFPrice,
Stock Integer(2) NOT NULL DEFAULT 0
CONSTRAINT AFStock,
Branch _id CHAR(5) NOT NULL,
PRIMARY KEY (Sku),
FOREIGN KEY(Branch_id) REFERENCES Branch) ON UPDATE CASCADE;







//apparel

CREATE ASSERTION aSTOCK
CHECK(not exist)(SELECT Stock
FROM Apparel
WHERE Stock > 10)
CREATE ASSERTION aPRICE
CHECK(not exist)(SELECT Price
FROM Apparel
WHERE Price< 0)
CREATE TABLE Apparel(
Sku VARCHAR(15) NOT NULL ,
Hoodie BOOLEAN(1) NOT NULL,
Shirt BOOLEAN(1) NOT NULL,
Price DOUBLE PRECISION XXXXXXXXXXNOT NULL
CONSTRAINT cPrice,
Stock INTEGER(2) NOT NULL DEFAULT 0
CONSTRAINT cSTOCK,
Branch_id CHAR(5) NOT NULL,
PRIMARY KEY (sku),
FOREIGN KEY (Branch_Id) REFERENCES Branch) ON UPDATE CASCADE;
//Comics
CREATE ASSERTION cSTOCK
CHECK(not exist)(SELECT Stock
FROM Comics
WHERE Stock > 10)
CREATE ASSERTION cPRICE
CHECK(not exist)(SELECT Price
FROMComics
WHERE Price< 0)
CREATE TABLE Comics (
Sku VARCHAR(15) NOT NULL,
Name VARCHAR(30) NOT NULL,
Date VARCHAR(10) NOT NULL ,
IssueNumber INTEGER(3) NOT NULL,
Price DOUBLE PRECISION XXXXXXXXXXNOT NULL
CONSTRAINT cPrice,
Stock INTEGER(2) NOT NULL DEFAULT 0
CONSTRAINT cSTOCK,
Branch_id CHAR(5) NOT NULL,
PRIMARY KEY (Sku),
UNIQUE (Name, issueNumber),
FOREIGN KEY(Branch_Id) REFERENCES Branch) ON UPDATE CASCADE;

//Manga

CREATE ASSERTION mSTOCK
CHECK(not exist)(SELECT Stock
FROM Manga
WHERE Stock > 10)

CREATE ASSERTION mPRICE
CHECK(not exist)(SELECT Price
FROM Manga
WHERE Price< 0)

CREATE TABLE Manga (
Sku VARCHAR(15) NOT NULL,
Name VARCHAR(30) NOT NULL,
Date VARCHAR(10) NOT NULL ,
Volume INTEGER(3) NOT NULL,
Price DOUBLE PRECISION XXXXXXXXXXNOT NULL
CONSTRAINT cPrice,
Stock INTEGER(2) NOT NULL DEFAULT 0
CONSTRAINT cSTOCK,
Branch_id CHAR(5) NOT NULL,
PRIMARY KEY(Sku),
Unique (Name, Volume),
FOREIGN KEY(Branch_Id) REFERENCES Branch) ON UPDATE CASCADE;


//Staff

CREATE ASSERTION MangerTypes
CHECK(exist)(SELECT Position
FROM Staff where position =”District Manager” | position =”Store Manager” | Position= ”null”)

CREATE TABLE Staff(
Staff_ID CHAR(5) NOT NULL,
Name VARCHAR(30) NOT NULL,
Position VARCHAR (15),
CONSTRAINT ManagerTypes,
Branch_id CHAR(5) NOT NULL,
PRIMARY KEY(Staff_Id),
FOREIGN KEY(Branch_id) REFERENCES BRANCH) ON UPDATE CASCADE;

//Renter
CREATE ASSERTION RFees
CHECK(not exist)(SELECT Fees
FROM Renter
WHERE Fees<= 0)
CREATE TABLE Renter (
Renting_id CHAR(5) NOT NULL,
Fees double(2) NOT NULL
CONSTRAINT RFees,
Rentername VARCHAR(30) NOT NULL,
Rentday VARCHAR XXXXXXXXXXNOT NULL,
Duedate VARCHAR XXXXXXXXXXNOT NULL,
Branch_id CHAR(5) NOT NULL,
PRIMARY KEY (Renting_id);
// Rentable_Comics
CREATE ASSERTION RentPrice
CHECK(not exist)(SELECT Price
FROM Manga
WHERE Price< 0)
CREATE TABLE Rentable_Comics (
Sku VARCHAR(15) NOT NULL,
Name VARCHAR(30) NOT NULL,
IssueNumber INTEGER NOT NULL,
Date VARCHAR(20) NOT NULL,
RentingPrice VARCHAR(20),
CONSTRAINT rentPRICE,
PRIMARY KEY(Sku),
UNIQUE(Name, IssueNumber),
FOREIGN KEY(Branch_id) REFERENCES Branch ON UPDATE CASCADE,
FOREIGN KEY(Renting_id) REFERENCES Renter ON UPDATE CASCADE,
Project Time table
Answered 5 days AfterMay 03, 2021

Solution

Neha Raghav answered on May 09 2021
32 Votes

Submit New Assignment

Copy and Paste Your Assignment Here