1) Design an ER diagram for keeping track of information about votes taken in the U.S. Senate during a session. The database needs to keep track of each senator, along with the name of the senator’s state, when the senator was elected and whether the senator is the junior or senior senator for the state. The database also needs to track each bill and needs to include 1) the BillName, 2) the DateOfVote on the bill, 3) whether the bill PassedOrFailed (domain is {YES,NO}, and 4) the Sponsor (senator who sponsored the bill). Finally, the database needs to include how each senator voted on each bill (domain is {Yes, No, Abstain, Absent}). Make sure to clearly label weak entity sets, identifying relations, existence dependencies, total participation, derived attributes, multivalued attributes, primary keys and weak entity set discriminators.
2) Use the ER diagram you generated above to produce a set of relations. For each relation specify candidate keys, primary keys and any foreign keys
3) Consider the schemae Videotape(videoId, dateAcquired, movieId, storeID ) and Store(storeID , street, city, state, zipcode, manager). Note that videoID represents a specific tape while movieID represents a movie. A) Specify the relational algebra expression that produces the videoID and dateAcquired values for managers whose last name begins with “S”. B) Find the set of movieId ‘s that are associated with all storeID ‘s (in other words, identify the movies that can be found at all stores).
4) Consider the schema: Letter_schema = (A,B,C,D,E). State whether the schema subject to a given set of functional dependencies is in BCNF. If it is not, decompose into BCNF relations that satisfy the lossless join property, and state whether the decomposition preserves functional dependencies.
a. A-> B, C and C->A,D,E.
b. A->B,C and D->E,A
c. A->B,C,D,E and B,C->A,D,E and D->E
d. A,B,C->D,E and E->A
5) Assume we have 4 data disk and one redundancy disk, that we are using RAID 4 and that a block consists of one byte.
a. Assume that that block1= 01000100, block2=11100001, block3=10101010 and block4 = 00001110. Calculate the value for the block on the redundant disk
b. Assume that we want to write 01 to bit positions 3 and 4 of block 2 and we want to maintain a correctly updated redundancy disk. How could this be accomplished using two reads and two writes? (Indicate what data would be written, what operations would be carried out and what data would be written).