Online Toy Store Management System
Refer the given Table structure and Data to Solve the problems.
TOY_STORE
Column Name
Data Type
Constraints
TOY_STORE_ID
NUMBER(3)
PRIMARY KEY
TOY_STORE_NAME
VARCHAR2(30)
NOT NULL
CITY
VARCHAR2(30)
Delhi, if not provided
PHONENUMBER
NUMBER(10)
UNIQUE AND NOT NULL
STORE_OPENING_TIME
TIMESTAMP
After 8 AM
STORE_CLOSING_TIME
TIMESTAMP
Before 10 PM
Data Type
Constraints
TOY_ID
NUMBER(4)
PRIMARY KEY
TOY_NAME
VARCHAR2(30)
NOT NULL, UNIQUE
TOY_PRICE
NUMBER(7,2)
> 10 , Mandatory
DISCOUNT_PERCENT
NUMBER(5,2)
< 90
AGEGROUP
NUMBER(3)
By default 5
TOY_RATING
NUMBER(1)
BETWEEN 1 TO 5
CATEGORY
CHAR(1)
Allowed I / O / B for Indoor/Outdoor and Both
TOY_PIC
BLOB(16M)
TOY_DTLS
Column Name
TOY_REL
Column Name
Data Type
Constraints
TOY_ID
NUMBER(4)
PRIMARY KEY.FOREIGN KEY(Toy_Dtls)
TOY_STORE_ID
NUMBER(3)
PRIMARY KEY,FOREIGN KEY(Toy_Store)
IN_STOCK
CHAR(1)
By default N, allowed Y/N
AVAILABLE_QTY
NUMBER(3)
Default 0
TCS Internal
TOY_STORE_ID
TOY_ID
TOY_STORE_NAME
CITY
PHONENUMBER STORE_OPENING_TIME
STORE_CLOSING_TIME
2014-04-01 21:42:05
1 Kid's Cave
Delhi
9912312312 2014-04-01 09:10:12
2 Kid's Corner
Mumbai
9912312321 2014-04-01 09:00:00
2014-04-01 20:00:00
3 Play and Grow
Mumbai
9912312301 2014-04-01 09:00:00
2014-04-01 20:00:00
4 Puzzles and More
Delhi
8112312301 2014-04-01 08:15:00
2014-04-01 20:00:00
5 Uncle Sam Toys Den Delhi
8112312310 2014-04-01 08:01:00
2014-04-01 20:00:00
6 Mickey Toys
8221312345 2014-04-01 09:00:00
2014-04-01 20:00:00
TOY_NAME
Delhi
TOY_PRICE
1 Bat
DISCOUNTPERC AGEGROUP
500
2 Ball
TOY_RATING
CATEGORY
50
3 Brainvita
250
10
5I
4 Scrabble
100
80
15
1I
40
2I
5 Snakes and Ladders
6 Chess
50
10
4I
7 Basket Ball
150
10
4o
8 Volley Ball
150
10
4o
30
2b
450
10
3o
50
3I
9 Ping pong Ball
10 Skates
11 Racing _--_ cars
TOY_ID
TOY_STORE_ID
TOY_PIC
IN_STOCK
AVAILABLE_QTY
1N
2Y
2Y
4N
4Y
12
4Y
16
Insert data in TOY_REL as per your choice, minimum 15 rows should be present.
Create views for following requirements
Display all numeric values to a precision of 2 decimal digits.
All comparisons should be case insensitive.
1.
2.
3.
4.
5.
6.
7.
8.
Create a view to contain maximum, minimum and average price of toys.
Create a view containing columns as Toy store name, city, toy name, toy price, toy rating, available quantity and age group.
Create a view containing columns like Toy store name, maximum available quantity, minimum available quantity, average
available quantity.
Create a view on table TOY_STORE. The view should contain only mandatory columns. Insert 2 more rows in the table using this
view.
Create a view on table TOY_DTLS. The view should contain indoor toys only.
Create a view on table TOY_DTLS. The view should allow only indoor (I) and Outdoor(O) values for the toy category. On inserting
or updating a value other than this, error should occur.
Create a view on TOY_REL and using it delete the entries where TOY_STORE_ID and TOY_ID =1.
Create a view on table TOY_DTLS. This view should not allow any sort of data manipulation through it.
TCS Internal
