Lab 1 - WebSubmit / Database Fundamentals


Objectives
  1. Review Lab 0. Problem with Lab 0 ?
  2. Familiarize ourselves with the WebSubmit procedure
  3. Discussion of a real-world Database Application


WebSubmit

The following link gets you to the homework submit webpage:

http://cs-websubmit.bu.edu/main.py?courseid=cs105

Using WebSubmit is straightforward. To familiarize yourself with the procedure, do the following:

  1. Create a test file with the text: "I rock my world" using the Notepad text editor or WordPad text editor available in the Accessories folder on Windows. (Start > All Programs > Accessories)
  2.    Remember: All of your homeworks should be submitted in plain-text form!  More information about creating a plain-text file is here
  3. Name the file lab1.txt.
  4. Login to WebSubmit using your Kerberos password.
  5. Submit your lab1.txt file to the lab01 project in WebSubmit.
  6. View your file in WebSubmit to make sure that you submitted the correct file.
  7. Change the content of the file to "I really do!", resubmit it, and view it again.

Practice Exercise: A Real-World Database Application

Let's consider one example of a real-world database application: an online catalog for a library.

  1. Based on the high-level description above, describe the types of data that could be stored in this type of database.
  2. Give an example of how transactions would be helpful in the context of this application.
  3. What are some other advantages of using a database system in this context? (It may help to recall the four key DBMS functions mentioned in the lecture notes on database fundamentals.)
  4. Let's devise a description of some of the tables that might be used by this application. This type of description is sometimes referred to as a schema for the database. It should include the name of the table, and the names of the fields that would be part of each record in the table.
    When determining the schema, it's helpful to follow this rule of thumb: a given table should describe either a single type of entity (person, place, or thing), or a relationship between two or more entities.
  5. For each table, it's helpful to have one or more fields whose values can serve as unique identifiers for the records in that table. These unique identifiers are referred to as key fields. For example, if we have a table of students, the BU id number can serve as a key. What key fields would make sense in the tables for this application?
  6. Identify and describe the use of primary and foreign keys in our library database application

Practice Exercise: Understanding keys and constraints

Consider the following instances of three relations:
      Passenger       Flight       BookedOn
     
id name city
001 Tom Brady San Mateo, CA
002 David Ortiz Santo Domingo, Dom. Rep
003 Kevin Youkilis Cincinnati, Oh
004 Ray Allen Riverside, CA
005 Kevin Garnett Greenville, SC
006 Zdeno Chara Trencin, SVK
     
number origin destination
DL5882 Boston, MA Indianapolis, IN
BA215 London, United Kingdom Boston, MA
AA573 Boston, MA Miami, FL
LH422 Frankfurt, Germany Boston, MA
UA717 Boston, MA Los Angeles, CA
UA898 Beijing, China Boston, MA
     
person flight
005 UA898
002 AA573
005 UA717
006 BA215
001 DL5882
004 DL5882

Notes:

  • Every person in the Passenger relation has a unique value for the id attribute.
  • Every flight in the Flight relation has a unique value for the number attribute.
  • The BookedOn relation captures the relationships between people and the flights on which they are booked. For example, the tuple (001, DL5882) in BookedOn tells us that Tom Brady (001) is booked on a flight from Boston to Indianapolis (DL5882).

Given these relations, answer each of the following questions:

  1. Select a possible primary key for each of these relations.
  2. Is the combination (number, origin) a key for the Flights relation?
  3. Is the combination (number, origin) a candidate key for the Flights relation?
  4. Two of the attributes in these relations appear to be foreign keys. Which ones?
  5. Give an example of a tuple that the DBMS would reject because it would violate a uniqueness constraint
  6. Give an example of a tuple that the DBMS would reject because it would violate a referential integrity constraint

CS105
CS105 Labs