1.3) Create a trigger, trg_customer_invoice, that increments num_invoices of the

Question

1.3) Create a trigger, trg_customer_invoice, that increments num_invoices of the corresponding customer whenever a new invoice is added. 1.4) Create a trigger, trg_new_line, that is fired when a new row is inserted in line table. The trigger does the following two actions: a. increments num_products of the corresponding customer b. updates invoice_total of the corresponding invoice to include the cost of the added products. 1.5) Create a stored procedure, prc_customer_discount, that takes two inputs, customer code and discount amount. The procedure then applies the discount to all invoices of the given customer by updating invoice_total values.
Question 2: Group by and Aggregates: Consider the following Hotel database:
Hotel (hotelNum, hotelName, city) Room (roomNum, hotelNum, type, price) foreign key hotelNum references Hotel (hotelNum) Booking (roomNum, guestNum, dateFrom, dateTo) foreign key roomNum references Room (roomNum) foreign key guestNum references Guest (g

uestNum) Guest (guestNum, guestName, guestCity)

Write SQL statements to answer the following:

2.1) How many hotels are there in the city of Minneapolis. 2.2) What is the average price for a room in the city of Edina. 2.3) For each guest, list guest number, guest name, and the number of bookings that are made by that guest. 2.4) How many different guests have made bookings that started in August of 2010? 2.5) Find the total number of bookings made by guests who live in London. 2.6) How many rooms of type ‘Family’ are there in the Minneapolis branch of ‘Holiday INN’?


Question 3: ERD design

Although you always wanted to be an artist, you ended up being an expert on databases because you love to cook data and you somehow confused database with data paste. Your old love is still there, however, so you set up a database company, ArtBase, that builds a product for art galleries. The core of this product is a database with a schema that captures all the information that galleries need to maintain. Galleries keep information about artists, their names (which are unique), birthplaces, age, and style of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored. Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lifes, works by Picasso, or works of the 19th century; a given piece may belong to more than one group. Each group is identified by a name (like those just given) that describes the group. Finally, galleries keep information about customers. For each customer, galleries keep that person’s unique name, address, total amount of dollars spent in the gallery, and the artists and groups of art that the customer tends to like.

3.1) Draw the ER diagram for the database. 3.2) Identify the relational schema for the database. Make sure to clearly indicate primary keys and foreign keys.

Explanation / Answer

1.3

Create trigger trg_customer_invoice after insert on invoice

for each row

update customer set num_invoices=num_invoices+1

where cusID=NEW.cusID

Question 2

2.1

select count(hotelNum) from Hotel where city='Minneapolis';

2.2

select avg(Room.price)

from Room inner join Hotel

on Room.hotelNum=Hotel.hotelNum

where Hotel.city='Edina';

2.3

Select Guest.guestNum,Guest.guestName,count(Booking.roomNum)

from Guest inner join Booking

on Booking.guestNum=Guest.guestNum;

group by Guest.guestNum,Guest.guestName;

2.4

select distinct count(guestNum)

from Booking

where dateFrom='August 2010';

2.5

select count(*)

from Bookings inner join Guest

on Booking.guestNum=Guest.guestNum

where Guest.guestCity='London';

2.6

select count(*) from

Room inner join Hotel

on Room.hotelNum=Hotel.hotelNum

where Room.type='Family' and Hotel.city='Minneapolis' and Hotel.hotelname='Holiday INN';

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.