Part 1 (20 points)

 

Assume that we have the following database for the Union-Times newspaper.

 

Most of these fields are self-explanatory – ask if you have a question. Filename is the filename that an article has, Photo_filename is the filename a photograph has. The value of Source is either Local, Freelance, Reuters or New York Times. ArticleDate is the date an article is written. PublicationDate is the date that an article is published. TakenDate is the date a photograph is taken. Byline is either Yes or No – whether the reporter's name is on the article. Paid is the amount of money paid to the photographer. We only store data about Reporters who work for the Union-Times (if an article is written by a New York Times reporter, that reporter will not have a corresponding entry in the REPORTERS table.

 

An article has at least one topic and at least 0 photographs.

 

ARTICLES(Filename, ArticleDate, PublicationDate, Source)

WRITES(Filename, ReporterName, Byline)

REPORTERS(ReporterName, StartDate, EndDate, Speciality, Salary, SocialSecurityNumber)

TOPICS(Filename, Topic)

PHOTOGRAPHS(Photo_filename, Photographer, Source, TakenDate, Paid)

PHOTOS_INCLUDED(Filename, Photo_filename)

 

Draw an entity-relationship diagram..

 

 

 

0..1
 
 

 

 

 

 

 

 

 

 


Part 2 (35 points)

 

Answer the questions in this part using the database from Part 1:

 

 

  1. Can a reporter have more than one speciality?

 

No. since it is a non-key field in the Reporters table. There can be just one Speciality for each ReporterName.

 

2.     Can an article be written by more than one reporter?

 

Yes, since ReporterName is part (but not all) of the primary key for the Writes table.

                               

 

 

  1. Can we include leave out the Topic in a TOPICS record?

 

No, it's part of the primary key.

 

 

  1. What would be a reasonable Validation Rule for the Salary field?

 

 

> 0

 

 

 

  1. What would be a reasonable Input Mask for the SocialSecurityNumber field?

 

 

000-00-0000

 

  1. What field in what table could use a combo box for data entry (with values coming from another table)?

 

Filename or ReporterName in WRITES. Filename in TOPICS. Photo_filename or Filename in PHOTOS_INCLUDED.

 

 

  1. What field in what table could use a combo box for data entry (with values coming from a list supplied by the designer)? Suggest values for this combo box.

 

 

Topic in TOPICS. Souce in ARTICLES or PHOTOGRAPHS. Speciality in REPORTERS.

 

 

 Part 3 (45 points – 15 points each)

 

The following tables are not 3NF. Convert them to 3NF. In the process, you are not allowed to add additional data fields.

 

1.        This table stores information about hiking in the Adirondacks. You can assume that all mountains have unique names. You can assume that you never hike the same mountain twice in one day. Trail Conditions are the condition of the trail for that day (muddy, icy, etc.). You can assume that Elevation and Distance never change.

 

(Mountain, Date, Elevation, Trail Conditions, Distance, Time)

 

 

 

(Mountain, Date, Trail Conditions, Time)

(Mountain,  Elevation, Distance)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.        This table stores information about tennis matches. You can assume that the names of all opponents are unique, and that you can play more than one person per day. Court is the location where the games occur. You can assume that you play tennis at no more than one location per day.

 

(Date, Court, Temperature, (Opponent, OpponentPhone, MyScore, TheirScore))

 

 

(Date, Court, Temperature)

 

(Date, Opponent, MyScore, TheirScore)

 

(Opponent, OpponentPhone)

3.     This table stores information about package deliveries. Each package has a unique PackageID.

 

 

(PackageID, Date, DeliveryAddress, CustomerID, CustomerName, CustomerPhone, Price)

 

 

(PackageID, Date, DeliveryAddress, CustomerID, Price)

 

(CustomerID, CustomerName, CustomerPhone)