Database Systems – Exam 1
When taking this exam you may use a single $3\times5$ card containing SQL syntax. The card must be written by you. Write your name on this paper and write your answers on the answer sheets provided.

  1. (2 pts each) Briefly define each of the following terms:
    1. relation
    2. primary key
    3. foreign key
    4. DBA (expand acronym and then define)

  2. (4 pts) What does SQL stand for and why is it important to know?

  3. (2 pts each) Briefly describe the types of data defined by each of the following SQL types:
    1. CHAR(10)
    2. VARCHAR(10)
    3. NUMERIC(8,2)
    4. SERIAL

  4. (2 pts) What is the difference between x='A%' and x LIKE 'A%' in the WHERE clause of a SELECT query?

  5. Consider the following three relations/tables that represent the domain of scheduling university classes.
    • room=(room_num,capacity,desc)
    • schedule=(id,course,inst_id,room_num,time_slot)
    • instructor=(inst_id,lastname,firstname,title)
    Assume that matching field names represent related fields among the tables.

    Table: room
    room_num capacity desc
    JSC 341 30 Main math room
    JSC 342 18 Math education room
    JSC 343 24 Computer science lab
    JSC 427 32 Consumer science room
    JSC 130 20 Biology Room
    Table: schedule
    id course inst_id room_num time_slot
    523 CORE 1033 534 JSC 341 A
    524 CORE 1033 534 JSC 341 B
    525 MATH 1003 716 JSC 325 A
    526 MATH 2014 398 JSC 427 D

    Table: instructor
    inst_id lastname firstname title
    534 Adams Joe Assistant Professor
    716 Tireless Tom Instructor
    398 Green Anne Professor
    322 Smithe Sammy Associate Professor

    1. (4 pts) Represent the tables graphically using UML notation. Be sure to specify the cardinality of relationships based on the structure of the tables and what you know of the problem domain.

    2. (3 pts each) Given the structure and contents of the tables above, show the output that would be produced by each of these queries:
      1. SELECT course, capacity FROM room NATURAL INNER JOIN schedule
      2. SELECT course, capacity FROM room FULL OUTER JOIN schedule USING (room_num)

    3. (3 pts each) Construct SQL statements to achieve the following results (do not assume that the contents of the tables exactly match the samples given above):
      1. Create the room table having field names and types as illustrated above. Also specify the primary key.
      2. Add an Associate Professor to the instructor table whose name is Mary Evans. Assign an id number of 598.
      3. Remove all classes scheduled in rooms that house fewer than 20 students.
      4. Assign all sections of “CSCI 1303” to be taught in “JB 105”
      5. List all course names and id numbers for whom no instructor has been assigned.
      6. List all course names that are taught by instructors who have the word “Professor” in their title.
      7. List the number of courses taught at each time slot. Order the list by time slot.
      8. List all rooms that have more than 5 courses taught in them.
      9. Create a view named all that lists all information for all classes including the full room information and the full instructor information. Have the courses ordered first by the course name then by the time slot.
      10. Use the view you created in problem 5(c)ix to produce a list of rooms (along with their descriptions) that are in use during time slot B.
      11. With a single query modify the capacity of all rooms whose capacity is less than 25 to have a capacity of 25 (i.e., make sure every room has a capacity of at least 25).
      12. Remove all rows from the instructor table.

  6. Suppose we have a database that lists all registered voters for Taylor county. The database is used to identify which polling station a voter is allowed to use and whether or not they have voted yet. The following information is to be kept. The SiteID and the SiteName represent the polling station. The SiteAdmins field identifies who the contact person for that particular site is along with their contact information. The VoterID, VoterName, and Voted fields provide information regarding a given voter.

    SiteID SiteName SiteAdmins VoterID VoterName Voted
    145 VFW Building Sam Jones (123-4567) 32657 Alice Aitken yes
    145 VFW Building Sam Jones (123-4567) 34567 Bea Bacon no
    145 VFW Building Sam Jones (123-4567) 56563 Clyde Caldwell yes
    145 VFW Building Sam Jones (123-4567) 34342 Drake Duncan no
    145 VFW Building Sam Jones (123-4567) 09238 Elias Egbert no
    476 First Church Amy Hu (234-5678 or 234-6789); Jan Evans (456-7890) 12342 Fred Flintstone no
    476 First Church Amy Hu (234-5678 or 234-6789); Jan Evans (456-7890) 23244 Greg Grigson no
    476 First Church Amy Hu (234-5678 or 234-6789); Jan Evans (456-7890) 34454 Heidi Hill no
    476 First Church Amy Hu (234-5678 or 234-6789); Jan Evans (456-7890) 45454 Ida Ingels yes
    476 First Church Amy Hu (234-5678 or 234-6789); Jan Evans (456-7890) 58688 Jeff Jordan yes
    567 United Mike Miller (679-2322); Kevin Klein (235-3433 or 236-2342) 23424 Lionel Liu yes
    567 United Mike Miller (679-2322); Kevin Klein (235-3433 or 236-2342) 93483 Nathan Nix no
    567 United Mike Miller (679-2322); Kevin Klein (235-3433 or 236-2342) 89343 Oprah Oliver no
    1. (6 pts) Name as many reasons as you can think of that this table is not in BCNF.
    2. (2 pts) Based on your answer to question 6a, how would you classify the form of this table (unnormalized, 1NF, or BCNF)?
    3. (4 pts) List the functional dependencies that exist in this table.
    4. (8 pts) Convert the table to BCNF. For each resulting table identify the primary key field(s). Show the table structure using list notation only (i.e., do not list the data in the table for your answer).