ICS 184: Database Systems
Assignment 2
Group 8

The Personal Database Application (PDA) we will be implementing is the Space Project (we’ll call the Space Database System – SDS). Requested by Candy Mamer, an administrative staff in ICS department, the Space Project is to facilitate space management of the whole ICS department. Typical purposes of Space Database System include:

Problem Sets

1. The four given ER diagrams can be accessed [here]
   1. Construct a database instance that conforms to the schema ER2 but not to schema ER1

      SS#, Account#, and Branch# are key for each...

      SS#	Account#	Branch#
      123456	1001	1
      123456	1003	1
      654321	1005	100

      The relationship <CAB> in ER1 specifies that each pair of (customer, account) has exactly on branch; each pair of (customer, branch) relates exactly to one account, and so each pair of (branch, account) with one customer.  The data above inherently violate the rules!

      ER2 allows pair (customer, branch) to have more than one account... and thus the above data conforms the ER2 schema.  Similar arguments will be applied for the next questions!

   2. Construct a database instance that conforms to the schema ER3 but not schema ER2:
      
      

      SS#	Account#	Branch#
      123456	1001	1
      123456	1003	1
      654321	1001	1

      
      

   3. Construct a database instance that conforms to the schema ER4 but not schema ER3
      
      

      SS#	Account#	Branch#
      123456	1001	1
      123456	1001	2
      123456	1003	1
      654321	1001	1

      
      

   4. Conver ER1 to another schema that only contains binary relationships

      
      

2. Given ER diagram:

   

   1. Problem and Solution:

      In the above schema Orders is the weak entity set and dependent on the strong entity sets, Customers and Items.  The problem is that an Orders entity does not have a Discriminator and, therefore, there is no way to correctly identify a particular Order among others.  Another "bug" is alive in the relationships between Orders and Customers, and Items.  According to the schema above, an Orders entity could have neither relation with Customers nor Items, which is inconsistent!

      One way to solve the problem is to add an attribute, OrderNumber, to the weak entity Orders to act as the discriminator for this weak entity set.  Also, make a constraint that the relations between Orders and Customers & Items to be TOTAL, so that a weak entity must have a relation to some strong entity.

   2. Below is a revised E/R diagram illustrating the above proposed solution:
      
      
3. Modify the given E/R diagram to reflect the changes