Dermatome Case study

Study the Dermatome case study presented in Appendix A. In what ways would a DB’S 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 DB’S, there Is less data redundancy, and hence less chance for data Inconsistency among offices. Lesser maintenance cost. Inning DB’S, Dermatome won’t need a large data processing team to maintain the data. It only needs a small IT staff to manage DB’S. 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 I-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 I-to-1/0 relationship because a staff member may manage O or 1 branch.

Staff supervises Staff This is a recursive relationship and is I-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 I-to-1/0 relationship Client holds Lease: It is I-to-1/0 if we do not archive leases (the O occurs if the client has not yet found a property to rent), or a I-to-many relationship if leases are archived. Newspaper advertises Properties: This will typically be a many-to-many relationship as Dermatome will use one newspaper to advertise multiple properties ND each property may be advertised in multiple newspapers.

Proportionate owns Properties: A I-to-many relationship as a private owner may list multiple properties, but each property is owned by a single owner. Businesswomen owns Properties: Same as the preceding relationship Staff oversees Properties: This Is a I-to-many relationship as each staff member may oversee multiple properties, but each property Is managed by a single staff member.

What queries do you think are required? Examples of queries required by the Branch user views: List the details of branches in a given city. Identify the total number of branches in each city.