Prior Exam

 

Name: __________________________________

70-455

 

For each of the following database schemas, indicate the current normal form for the schema as given. If the schema is not in 4th normal form, convert it to 4th normal form. The tables listed below may only represent part of the entire database schema. When necessary, you only have to normalize the table(s) listed. (5 points each)

The following database contains information about the equipment connected to and used for a network in an organization.

Assume the following functional dependencies are true:

Serial# ® EquipDesc, Vendor, EquipType, Room#, NetworkSpeed

EquipType ® EquipDesc, Vendor, NetworkSpeed

DataCable# ® Room#, Desk#

BackboneCable# ® Room#, Room#

Person, PC# ® Desk#

Desk# ® PC#

PC# ® NetworkSpeed, PCManf

Vendor ® VendorContact, VendorPhone

LANID ® LANDesc, NetworkSpeed

WANID ® WANDesc

Room# is a unique identifier for a cabling closet.

Desk# is a unique identifier for a desk.

PC# is a unique identifier for a PC.

DataCable# shows the connection between a desk and the cabling closet.

BackboneCable# shows the connection between two closets.

People can work on different PCs at different desks but can only be at a single

desk if working on a specific PC.

A desk only has a single PC at it.

 

A) WorkLocation (Person, PC#, Desk#)

B) Equipment (Serial#, EquipDesc, Vendor, VendorContact, VendorPhone, EquipType, Room#, NetworkSpeed)

C) LAN (LANID, LANDesc, NetworkSpeed, {Serial#, EquipDesc, EquipType, Room#, Vendor}, {DataCable#, Room#, Desk#}, {PC#, PCManf})

D) WAN (WANID,WANDesc)

WANEquip(WANID, Serial#)

Equipment(Serial#, EquipType, EquipDesc, Room#, Vendor)

WANCable(WANID,BackboneCable#)

BackboneCable(BackboneCable#, Room1#, Room2#)

Using the database schema provided, write the SQL for the following. (5 points each)

A) In office number order, list the names of the managers of all employees where the employee has not made a sale since 1/1/1999.

B) Create the SalesHistory table so that an employee cannot be deleted if he/she has made any sales and updates made to employee ID in the employee table are automatically reflected in the history table.

C) Increase the value of all gifts available to purchasers of single family houses (not townhouses) by 15%.

D) For each manager, list his/her name and office number and give the total number of employees, the average property listing price, and the highest and lowest sales price for a property previously listed by the manager’s office.

E) Delete all properties in NJ that have swimming pools and whose listing office includes any employee whose last name is "Smith".

F) For all offices whose average listing price is greater than $125,000, list the office number, manager’s name, and the number of listings.

G) List the office number and city of the offices whose average listing price is greater than the average listing price for all other offices.

H) List the office number, city, and state of all offices that only have property listed where all owners owns at least 25% of each property listed.

Bonus) By city and state, count the number of listed properties for which no single owner owns more than 25%

Bonus) List the address, description, city and state for all properties which are currently listed at a price that is lower than a price the property previously sold for

 

4. Using the database schema provided, write the SQL for the following. (10 points each)

4A. I have just received a compliment from a customer about an employee named Sue "something starting with T" who works in one of the central Illinois offices. Knowing that the zip codes in central Illinois start with "61" and that her first name could also be Susan, get a list of possible employee names with their manager names and office phone numbers. The name definitely was not Suzi, Suzanne, or Susi so names like that should not be returned.

4B. The list returned had too many names on it. The compliment was in regard to the recent purchase of a townhouse (type = 'TH"). Add a restriction to the list just returned to only include employees who have sold a townhouse in the month of November 2000.

 

4C. Much better. The latest list only returned two names, Susan Turner and Sue Thomas. However, I recall that a couple of the offices in central Illinois currently don't have managers assigned to them. The mysterious "Sue T" might work in one of these offices. Change the query for 4B to also include just the name and office phone number of any Sue or Susan T's who meet the other criteria but work in an office that doesn't have a manager assigned to it.

Hint: Remember that in order to use a UNION the same number and type of items need to be returned from each of the SELECTs that are UNIONed. When you wish to pull together the results from two SELECTs where this is not the case, you can SELECT a constant value. For example, if you have two selects where the first returns three strings (A, B, and C) and the second returns only two strings (A and C), use the following to get the desired result:

SELECT A, B, C FROM …

UNION

SELECT A, ' ', C FROM …

4D. Almost got it. There is also a Susan Taylor that seems to meet the criteria. The only other piece of information that I have to go on is that the customer was especially happy because her new townhouse included the handicap-accessible features that she needed for her mother. Write a query that will find the correct Sue or Susan. (This query can use the specific names identified up to this point.)

4E. List the property ID, address, city, and state for all property that is for sale at a price less than the average price for all property in the same city and state.

4F. List the office number for all offices whose average price of property for sale is greater than the average list price of at least one other office.

 

 

 

 

 

NOTE: This schema is slightly different than the one handed out in class earlier.

 

Double underlining indicates a primary key that is also a foreign key.

PC (PC#, Location)

Employee (EmpID, Name, MgrFlag, OfficeNbr)

Manager (EmpID, OfficeNbr)

Office (OfficeNbr, Address, City, State, Zip, PhoneNbr)

Property (PropertyID, Address, City, State, Zip, Description, Price, OfficeNbr)

PropertyType(PropertyID, Type)

SingleFamHouse (PropertyID, Gift#)

SingleFamTHouse (PropertyID, Gift#)

Gift (Gift#, Description, Value)

Owner (OwnerSSN, Name, Address, City, State, Zip)

PCAccess (PC#, EmpID, AccessType)

MgrPCAccess (PC#, EmpID, OfficeNbr,AccessType)

SalesHistory (PropertyID, EmpID, SalePrice, Date)

PctOwned (PropertyID, OwnerSSN, PctOwned)

 

 

 

 

 

 

The following is part of an E-R model for an insurance company. All attributes are given.

 

 

Of the three possible schemas that could be developed, which would you recommend implementing and why? (20 points)

Give 3 different 4th normal form schemas for this E-R Model. (15 points)

 

 

Below is documentation for a department store's bridal registry system.

A bridal registry is a method where a bride and groom go to a store and select items that they would like to receive as wedding presents. They then notify the people invited to their wedding at which store they have registered. The wedding guests can then go to the store and review the registry in order to select items that are listed on it. The advantage of this is that the bride and groom are given gifts that they would like to receive and the guests are able to get a gift that is desired. In the past, bridal registries have been used for items like fancy china dishware, crystal glasses and stemware, and sterling silverware. Guests could then buy something as inexpensive as a set of salt and pepper shakers or as expensive as several place settings of sterling silver. While still used in the fine china and silver departments of stores, bridal registries are increasingly also being used for things found in other departments of the store, like furniture, hardware, and power tools.

A department store has a bridal registry. This registry keeps information about the bride, the products that the store carries, and the products for which each bride registers. Some products include several related items: for example, dish sets, include plates, specialty dishes, and serving bowls. Brides typically register for a large number of products, and many brides register for the same products. A bride may also register for several of the same product but may register for different colors or types of each product. For example, a bride may register for bath towels, which typically come in sets (bath towel, hand towel, wash cloth), and may register for 4 blue bath towels, 4 blue/white striped bath towels, 8 blue hand towels, and 16 white wash cloths. The registry also must keep track of what registered items have been purchased for the bride so that a current list showing items registered for but not yet purchased can be produced for friends and family who come to the store. Information kept about brides includes name and finance's name, address and phone number. Product information includes product number, description, and store department in which the product can be found.

Brides (and grooms) come into the store and register for items that they would like to receive as wedding gifts, for example, a china pattern or sterling silver dinnerware set. The couple then notifies people who are invited to the wedding that they are "registered" at the store. Individuals wishing to purchase something for the couple then come into the store and are given a copy of the registry that shows items that have not yet been purchased. They can then purchase items on the list which then have to be marked as having been bought. For example, someone may come in and buy only one place setting of the bride's china. If eleven other people do the same thing, the bride will receive twelve full place settings of new china.

 

 

Complete the following based on this description.

    1. Entity-Relationship Diagram (attributes may be omitted)
    2. 4th Normal Form Database Schema

      3. (properly identify all primary and foreign keys)

    3. Create SQL for the following, using your schema from #2:

3A. Create any of your tables from #2, insert a row in that table, change the row just inserted, delete that row, delete the entire table.

3B. For Bride "Jane Smith" whose wedding is on 1/31/2001, print the current registry showing only items (with number remaining and description) that have not yet been purchased.

3C. List the bride/groom and wedding date for all people registered whose wedding is scheduled for next month.

3D. Give the item number and description for the item that is most often registered for by a bride and then not purchased. (This should only include people whose wedding was prior to today and should take into account the number of each item requested by the bride.)

3E. Give the item number and description for the item that is most often registered for by a bride and then purchased by someone. (Same restrictions as 3D).

3F. List the bride and groom whose registry resulted in the highest total sales for the store.