This assignment represents the conceptual design phase of a database project using an E/R diagram (and English). The next assignments will include the logical design, the creation of the database on the relational DBMS ORACLE, and processing of the database. Each project is based on the solution provided in class to the previous one. Therefore, NO LATE ASSIGNMENT ACCEPTED. KEEP a copy of all the work you turn in. Justify and explain all your design decisions.
You are requested to design a database for COLLPROP, a Property Management Company. The main purpose of the database is to help COLLPROP manage the rentals and maintenance of apartments in apartment complexes. For each complex, COLLPROP signs a property management contract with the owners, specifying the dates of contract, the management fee, and a deposit paid by the owner for various expenses by COLLPROP. An COLLPROP salesperson and his/her commission are also specified in the contract, as having brokered the contract. Complexes may have several owners (assumed to be persons). Each complex is assigned a manager, who is a COLLPROP staff member. A manager is in charge of one complex only.
Several tenants may sign a lease for an apartment. Extra "cotenants" may be accepted during a lease: COLLPROP must be informed about them and keep track of them, however they are not responsible for the lease. A tenant may rent one apartment only at any given time. The lease indicates the beginning and ending dates, the monthly rent, and security deposit.
Would-be tenants (possibly including current tenants) apply with COLLPROP for rental of apartments in complexes. An application to an apartment has an application date and specifies a complex and what is desired: number of bedrooms, rental desired starting date, floor number, and so on. It does NOT indicates a specific apartment. A small deposit must be provided with the application. An applicant may have multiple rental applications. An application may be made through an COLLPROP salesperson.
There may be requests for repair of apartments or complexes. A request for a repair is assigned a unique identifier, has a date, and a description of the problem. COLLPROP will assign one of several employees to a repair request, and a starting date for the repairs. The manager of the complex requiring a repair (or containing the apartment requiring the repair) is responsible for following through the completion of the work.
Complex:
?Complex name?(primary key)
?Street address, city, zip
Number_of_apts
?amenities (such as swimming pool, fitness room, tennis, ...); a
complex may have several amenities
?
Apartments: are identified by an apt number within their complex?
(an apartment number is unique within a given complex, but is not
unique relative to all complexes)
have: num_of_br, num_of_ba, sqft, floor_num
All persons in the database (tenants, owners, COLLPROP staff members) have at a minimum: SSN (primary key), lname, fname, bdate, sex
Presentation Guidelines
Please only one report per team: write all teammates names on your project + one email address on the cover page. Each teammate must sign the cover page before the project is turned in.
Your project will include one E-R diagram, using the conventions described in class (Koster textbook) and a short English explanation. If you think something in the project description is ambiguous or incomplete, explain why, and provide your own reasonable interpretation (at your own risk). Questions to clarify the project are welcome. All names must be unique (entity names, attribute names, relationship names) and meaningful.
Explain:
* Each entity; indicate name, must be same as on diagram. Omit the obvious attributes in your explanation, such as sex or DOB. Indicate which attributes are multi-valued, indicate the key or the partial key (for a weak entity). If an entity is a weak entity, explain why and indicate its identifying relationship and the other entity involved in its identification.
* If the entity is part of a generalization hierarchy, explain it in that context.
* Generalization hierarchies. Give a brief description. Make sure that common attributes are placed at the highest level possible. Indicate exclusive versus overlapping, partial versus total, and explain.
* Each relationship (indicate its name, which must be same as on the diagram). You must be able to explain very clearly the meaning of each relationship and in particular explain what very specific information is provided by the relationship. Indicate and explain, for each relationship, the attributes, the maximum and minimum cardinality. You may clarify your explanation with specific instances between entity instances. Each relationship must be explained in one small paragraph in one place, not in small sentences in various places of the project.
Do not be exhaustive with the attributes. Only show the ones discussed in the project description. I am more interested in making sure that you show a good understanding of the concepts of relationships and generalization hierarchies.
The project report must be typed. E/R diagrams must be drawn using some software (e.g. Word, Powerpoint, Visio, or SmartDraw, which can be downloaded for a free trial). Whatever software you use, you are required to use the conventions of Koster textbook (Crow's foot)
Show one ERD only. If the software you use has some symbols not in Koster, please white them out. If it misses some Koster symbols, please add them by hand.
Your diagram should be as readable as possible. OK to have a diagram on one 11" by 17" page or two 8" by 11" pages attached together as one large page.
Do not show foreign keys on the diagram. A "foreign key" indicates: redundancy (that is the information is already provided by a relationship) or the existence of a relationship not shown on your diagram.
An E/R diagram without any explanations is only worth 50% of the full grade.
If possible, your E/R design will have at the very least one of each of the following: a 1-1 relationship, a 1-M relationship, an M-M relationship, a weak entity and its identifying relationship, a multi-valued attribute, a generalization hierarchy, an associative entity. Obviously, the choice of these elements must be appropriate to earn credit. Remember that a relationship may connect any entities, whether they are in a generalization hierarchy or not.