Reference no: EM13977289

Media Support Services (MSS) is tasked with satisfying the Audio/Visual (AV) needs of the university across its major functions. For example, the MSS group is in charge of installing interactive technologies in classrooms, conference rooms and even in operating theatres across the campus. It goes without saying that MSS manages a large inventory of devices. You have been asked to develop a database solution for MSS that keeps track of their inventory across campus, manages MSS projects across campus.

Draw an ERD for the scenarios below. Ensure that your diagram is complete such that it includes: all primary and foreign key attributes, all non-key attributes and all associative entities. For this assignment, ensure that where a M:N relationship is identified, you fully specify the associative entity - do not stop at descriptions of relationship attributes.

1. MSS maintains a list of device types that it supports. Each device type is described by a unique device id number, a service start date (the date when MSS began to stock this device) and a service end date (the date that MSS plans to stop supporting this device). Every device has a model code. These need to be recorded as well. Finally MSS wants to track the quantity it has on hand for each device type (QoH). (10pts)
2. Every device has a manufacturer. Manufacturers are described by a unique id, a name, a quality rating, a support telephone number and a vendor contact name. MSS often stocks many devices produced by the same manufacturer. There is never a need to store a manufacturer from which MSS has purchased no device type. (10pts)
3. MSS stocks many units of each device type. Each unit is identified by its device type id and a serial number. MSS wants to keep track of the date it received delivery for each and every unit it has in-stock. If a unit has been deployed to a room on campus (i.e. classroom, surgical theater, conference room etc), then MSS needs to record the date of its deployment. (15pts)
4. The rooms to which MSS devices are deployed need to be recorded. A given room (i.e. classroom, surgical theater etc) may contain several MSS devices. A given MSS device may be deployed, rather obviously, to only one room, and where the device is still in the MSS store room, it is deployed to no room. Rooms are identified by a building code, a room number, and a primary room function. Because there is a known and finite set of room functions, MSS would like to maintain a standard list of room functions. Each room function has a function code, a function name, and a function description. Some rooms have several functions. All rooms must have a function. Because there is a known and finite set of buildings on campus, MSS would like to maintain a standard list of buildings. Buildings are identified by building code. They are described by their street address and by their name. In addition, MSS would like to record whether or not the building is part of the Monroe Park Campus or not. Every room must be located within a building.Allbuildings contain multiple rooms. (20pts)
5. MSS is engaged in many projects across campus. A project is a request by a University department for the installation of devices in either a single room, or in several rooms. A given room may have been the focus of many projects over time. The actual date of completion of work on a particular room that results from a particular project must be stored. Work on a particular room as part of a particular project entails the installation of device units. Many device units may be installed in a particular room as a result of a particular project. A project has a unique project code, a start date, a projected end date and an estimated cost. (20pts)
6. MSS keeps a record of all University departments for which it has initiated projects. It is possible that a department has initiated several projects over time. Departments do not collaborate on projects, each project is initiated by one department. All departments have a budget code which uniquely identifies them. MSS also needs to record the names of the departments and the name, phone number and email address of its contact within each department. (10pts)
7. Finally, each MSS project is assigned to one or more of MSS's project managers. A particular project can be assigned to many project managers, however, over time, a project manager would have completed several projects. MSS maintains a record of every project it has undertaken. Each project manager working on a project must give an evaluation of the project. MSS wants to store these evaluations. A project manager is identified by an employee identification number. Other characteristics of project managers are their first and last name, certifications, and hire date.