Homework 1 Solutions

1.6 Advantages and Disadvantages of DBMSs: See Section 1.6 for a complete list of the advantages and disadvantages of DBMSs. The ones that I stressed in class were:
1. Advantages
  1. Flexibility for the End User (the book calls this "More information from the same amount of data"): The big advantage of DBMSs is that they provide a declarative way to specify queries, so that users can quickly specify the data they want, without specifying how to obtain it. For example, to obtain the information about the names of the clients that live in Joe Keogh's properties, an employee could write the following query:
```
SELECT Client.fName, Client.lName 
    FROM Client, PrivateOwner, PropertyForRent, Lease
    WHERE PrivateOwner.lName = Keogh AND PrivateOwner.fName = Joe
       AND PrivateOwner.ownerNo = PropertyForRent.ownerNo
       AND PropertyForRent.propertyNo = Lease.propertyNo
       AND Lease.clientNo = Client.clientNo
```
    1. The DBMS would be responsible for translating the query into a series of operations for accessing the information and returning it to the user.
    2. Note that the user only needs to know about the logical organization of the data. The user does not need to know anything about the physical organization of the data.
    3. This means that the user can write the query and immediately obtain the report, rather than having to place a request with DP to write a program to create the report.
  2. Control of data redundancy: Databases provide ways to "decompose" files so that similar information gets placed in one file. For example, all the information about a property being rented could be placed in a single file, rather than in two different files in Sales and Contracts.
  3. Data integrity: By eliminating redundancy, the risk of inconsistent data is reduced.
  4. Sharing of data: The corporate data is kept in a centralized repository, which makes it easier to cross reference data that used to be kept in separate departments (such as the earlier query involving the clients living in Joe Keogh properties).
  5. Decreased maintenance costs
    1. Since physical structure is not hard-wired in application programs, it is easier to change data without changing the underlying programs (a program should use a "catalog" to look up the size of a field rather than hard coding it).
    2. Backups are easier because data is centralized
    3. Security is easier to enforce because file access goes through one central location
    4. Concurrency is easier to control because file access goes through one central location and is less ad-hoc than when many different independently written programs are trying to access the same files simultaneously
2. Disadvantages
  1. Complexity and Cost: Because of all the services they provide, DBMSs are very complicated pieces of software that typically cannot be written by a single DP department.
    1. Requires that you purchase the DBMS from a vendor, which makes it more difficult to tailor the system to your needs
    2. May require yearly licencing fees
  2. Size and Hardware: The complexity and size of a DBMS means that it is a large piece of software that may occupy many megabytes of disk space and require substantial amounts of memory to run efficiently. A set of custom-tailored programs written by DP might require much less disk storage and main memory.
  3. Performance: Hand written code for a file-based system will often out perform a more general DBMS which may not have the same domain specific information available for optimizing the query.
  4. Greater impact of a failure: Because the data is centralized, a hardware or software failure may have a greater impact because all data operations will come to a halt.
  5. Increased vulnerability to data theft: Because the data is stored in one centralized location, hackers can gain access to all of your organization data if they can break into the database. When the data is distributed under the old file system approach, your organization will only suffer a partial data theft if one of the data systems is successfully hacked.
1.11 DreamHome Case Study
1. Ways in which a DBMS could benefit DreamHome: There are any number of benefits a DBMS could provide for DreamHome and I'll accept any answer that provides a good discussion of a few such benefits. Basically you want to map the advantages of DBMSs to DreamHome's specific circumstances. Amongst the ones I thought of were:
  1. Easy report generation capabilities for staff: Staff members are unlikely to be computer savvy. However, they can easily generate the reports they need using the declarative query facilities of SQL.
  2. Data sharing: The database will store information used by human resources, contracts and leasing, and the sales office. Rather than scattering the data across these different departments, or even worse, across the different branches, the database will store this information in one centralized repository and allow users from different departments to access information that might normally be stored in a different department.
  3. Data integrity and control of data redundancy: If each of DreamHome's three different departments independently stored the data they needed, there would be a good chance that data would be duplicated, such as the sales office and the leasing office duplicating information about clients and property owners. Often times duplicated information leads to data inconsistency, such as when one office updates some renter information and fails to notify the other office to update its information on the renter, or when the two offices simply record the data differently, such as spelling a renter's name differently. By storing the data in a centralized DBMS, there is less data redundancy, and hence less chance for data inconsistency.
  4. Decreased maintanance costs: DreamHome is probably not a huge company and hence it would be burdensome to support a large data processing team to maintain the data and write software to prepare reports for users. If DreamHome uses a DBMS, it only needs to maintain a small IT staff to manage the DBMS.
2. Main DBMS Objects: The book pretty much lists the main objects you will be modeling:
  1. Branches
  2. Staff
  3. Managers
  4. Properties
  5. Private property owners
  6. Business property owners
  7. Clients (renters)
  8. Leases
  9. Newspapers
3. Relationships among objects: It suffices to enumerate several of the potential relationships. Here are a number of them with the relationship boldfaced (I call some of the relationships 1-to-many but many-to-1 is okay for this exercise because that's the term I used in class):
  1. Branch has staff: This is a 1-to-many relationship since one branch has many staff members, but each staff member belongs to only one branch.
  2. Staff manages Branch: This is a 1-to-1/0 relationship because a staff member may manage 0 or 1 branch.
  3. Staff supervises Staff: This is a recursive relationship and is 1-to-many, since one supervisor may manage multiple staff members, but each staff member is supervised by only one manager.
  4. Staff, Branch, Clients have a registers relationship: This is a tertiary relationship in which a staff member registers a client at a branch. Each staff and branch may have multiple clients, but a client is registered with a single staff member and branch, so in some sense it is 1-to-many from client to staff/branch. I don't expect you to have mentioned this relationship. We will get to tertiary relationships in Chapter 12.
  5. PropertyForRent leasedBy Lease: The multiplicity of this relationship depends on whether we keep old leases, whether a group of tenants that occupy the same property sign individual leases or one lease, etc. Hence it could be a 1-to-1/0 relationship (no archiving of leases and 0 if the property has no current lease), or a 1-to-many relationship (a group of tenants signs individual leases or we archive leases).
  6. Client holds Lease: The multiplicity of this relationship depends on whether we archive old leases. It is 1-to-1/0 if we do not archive leases (the 0 occurs if the client has not yet found a property to rent), or a 1-to-many relationship if leases are archived.
  7. Newspaper advertises PropertyForRent: This will typically be a many-to-many relationship as DreamHome will use one newspaper to advertise multiple properties and each property may be advertised in multiple newspapers.
  8. PrivateOwner owns PropertyForRent: A 1-to-many relationship as a private owner may list multiple properties, but each property is owned by a single owner.
  9. BusinessOwner owns PropertyForRent: Same as the preceding relationship.
  10. Staff oversees PropertyForRent: This is a 1-to-many relationship as each staff member may oversee multiple properties, but each property is managed by a single staff member.
4. Details that need to be stored with each object: Appendix A.1 lists the details that need to be stored with each object.
5. What queries do you think are required: Appendix A.1.2 lists a good group of sample transactions.