DREAMHOME_case study
1. Study the DreamHome case study presented in Appendix A.
In what ways would a DBMS help this organization?
Ease in generating reports staff using declarative query facilities of SQL
Centralized data sharing. Database will store information in one centralized repository and allow users from different departments to access information.
Data integrity and control of data redundancy. By storing the data in a centralized DBMS, there is less data redundancy, and hence less chance for data inconsistency among offices.
Lesser maintenance cost. Using DBMS, DreamHome won’t need a large data processing team to maintain the data. It only needs a small IT staff to manage DBMS.
What data can you identify that needs to be represented in the database?
Branches
Staff
Managers
Properties
Private property owners
Business property owners
Clients (renters)
Leases
Newspapers
What relationships exist between the data items?
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.
Staff manages Branch: This is a 1-to-1/0 relationship because a staff member may manage 0 or 1 branch.
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.
Properties leased By Leases: It could be a 1-to-1/0 relationship
Client holds Lease: 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.
Newspaper advertises Properties: 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.
PrivateOwner owns Properties: A 1-to-many relationship as a private owner may list multiple properties, but each property is owned by a single owner.
BusinessOwner owns
In what ways would a DBMS help this organization?
Ease in generating reports staff using declarative query facilities of SQL
Centralized data sharing. Database will store information in one centralized repository and allow users from different departments to access information.
Data integrity and control of data redundancy. By storing the data in a centralized DBMS, there is less data redundancy, and hence less chance for data inconsistency among offices.
Lesser maintenance cost. Using DBMS, DreamHome won’t need a large data processing team to maintain the data. It only needs a small IT staff to manage DBMS.
What data can you identify that needs to be represented in the database?
Branches
Staff
Managers
Properties
Private property owners
Business property owners
Clients (renters)
Leases
Newspapers
What relationships exist between the data items?
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.
Staff manages Branch: This is a 1-to-1/0 relationship because a staff member may manage 0 or 1 branch.
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.
Properties leased By Leases: It could be a 1-to-1/0 relationship
Client holds Lease: 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.
Newspaper advertises Properties: 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.
PrivateOwner owns Properties: A 1-to-many relationship as a private owner may list multiple properties, but each property is owned by a single owner.
BusinessOwner owns