Assigned:
January 31th, 2008
INFS 614 -- Prof. Smith
Assignment 1a: Due Date Feb 7th, 2008 at 7:20 pm.
Problem 1. Every weak entity set can be converted to a strong entity
set by simply adding appropriate attributes. Why, then, do we have weak entity
sets?
Problem 2 Give a 1-m relationship set example, i.e., give two
entity sets and the (binary) relationship set between them. The examples must
be ``realistic'' and meaningful. For each entity set, discuss whether it has a
``full participation'' in the relationship set (explain why).
Repeat the above for a 1-1 relationship
set.
Problem 3. Suburban Virginia University (SVU) has decided to
consolidate the functionality of three small overlapping database systems which
support applications for 1) teaching (e.g. instructor assignment and
evaluation), for 2) registration (e.g. online course status, waiting lists),
and for 3) student records (e.g. transcript generation).
The resulting new system will support the
following enterprise description: Professors and GTAs are assigned to
administer the sections of each class being offered in a semester. At the end
of the semester, they get a "team rating" (professors and GTAs together
get one rating per section). To support the assignment of professors to
sections, a record is kept of which class each professor can teach. Classes can
have one or more prerequisite classes. Students can take several sections each
semester, and receive a grade for taking each section. Students may end up
waiting for some sections, and receive a "rank" (determining the
order they will be admitted if other students drop). However, no more than 10
students can wait on a class at the same time. Note that GTAs are students,
however they differ in that they have a salary. All people (e.g. students,
professors) are uniquely identified by their social security number. All
classes are identified by department name (e.g. "INFS") and course
number (e.g. "614"). Sections of classes are distinguished by their
section number (e.g. "02").
Given this functional description of the
business processes at SVU, the following is Homework 1a, and is due at 7:20pm
Feb 7th, 2008.
1. Draw an ER-diagram for the database, identifying the
following: (i) all the entity sets, (ii) all the relationship sets and their
cardinalities (key constraints, i.e. "many to many", "one to
one", etc.), and (iii) the primary key for each entity set (and weak
entity set, if any) and each relationship set. Invent your own attribute(s) for
the entity sets (in addition to any mentioned).
2. Indicate (what and why) feature(s)/property(ies) in the above description that are NOT captured by your
ER-diagram.
3. Which entity and relationship sets in your diagram
participate in the generation of a section roster? Of a student transcript? How
are a section roster and a student transcript alike? How do they differ?
Homework submission. Submit a
paper copy of your solutions to problems 1, 2, and 3.
Assignment 1b: Due Date Feb 14th, 2008 at 7:20 pm.
Given your ER diagram from
assignment 1a, do the following:
Problem 4 Convert
the entity-relationship design (from Problem 3) to a scheme for a relational
database (on paper). List all relation schemes. For each relation scheme, state
(i) the name of the relation, (ii) the names of its attributes, (iii) the
domain (or data type) of each attribute, and (iv) the primary key.
Problem 5 Use
Oracle to create the tables from Problem 4 above and insert at least two tuples
to each table. Implement all these in one script (Text) file. Your
script must run on Oracle on the ISE machines.
Homework submission. Submit a
paper copy of your solution to Problem 4, and a paper copy of the script file
for Problem 5. In addition, submit an electronic copy of the script file for
Problem 5. See the web site for
instruction on how to submit electronically. (When assignments have a paper and
electronic components, the deadline is the same for both.)
END