Attributes and Primary Keys of the ERD - Coursework Example

Attributes and Primary Keys of the ERD 1. The ERD diagram is an analysis tool that helps users to identify entity types, relationship types, attributes and high-level ERD constructs. To come up with the ERD diagram, I adopted the following steps. (i) Identification of the entity types which included weak entity types of the company. (ii) Identification of the relationship, both ISA and ID types. (iii) Classification of the relationship types identified in step ii, according to multiplicity. That is if it is one-to-one, many-to-one or many-to-many. (iv) Determination of the participation constraints for each entity types in each relationship type. (v) Drawing the ERD with the entity types and the relationship types and their underlying domains. (vi) Identification of the attributes of entity and relationship types and their underlying domains (vii) Identification of the primary keys for the entity types (viii) Addition of the attributes and primary keys to the ERD The conceptual ERD diagram The sound engineers are required to check the equipment before issuing them out to customers. The studio manager should keep records of the items issued to customers. The record should include the date borrowed and the date and time the equipment should be returned. The underlined attributes are the primary keys. They are used to give reference to a specific item. 2. To identify the entity types, I broke down all the components of the company. I used the understanding that an entity has attributes, which form its characteristics. The entity types represent sets of objects and are usually pictured by rectangular nodes as shown in the ERD in question 1. SoniDream, is the company, forming one of the entities. Within it, there several other entities, derived from their various attributes. I, therefore, considered how each of the following has different characteristics from the other (Moreira et al. 2013, p.455). The company, with a main office headquartered in London. A company as an entity must have attributes such as physical location. In this case, its physical address is London. A company must also have the people who run it. Using this knowledge, I identified members of staff who fall in different categories and play different roles. They include studio managers, sound engineers, and van drivers. Recording studios distributed over different parts of London. Studios are fixed entities and must therefore, have their physical locations. In this case of the studios are in London and the other 8 are located in different parts of the UK. Equipment used in the studio is an entity and must be comprised of different pieces. In this case, they included recorder, mixing desks, studio monitors, controllers, microphones, converters, musical instruments, and other music accessories. The different entities are joined by different relationships. 3. I identified the main relationship types for SonicDream using a hierarchical order. A relationship type is an association between two or more entity types. Relationship types may associate an entity type with itself. In such a case the roles of the entity types in the relationship type will be listed on the edges like shown in the ERD in part 1. Such a relationship is known as recursive. The different entities in the company have one thing in common, which is they are all tied to the company as the main entity. The 4 recording studios in London and the other 8 in the rest of UK are subsidiaries of SonicDream. The managers of the studios have to consistently report to the head office from where instructions are issued. The studios are therefore, below the main office in the hierarchy. However, all the studios are at the same level but have to liaise with each other. For instance, when a customer requests a certain piece of equipment, the manager of the specific studio may contact the other studio to check whether that particular equipment is available for hire. When it comes to members of staff, they are at different levels in the hierarchy. For instance, managers are at the top, while van drivers and sound engineers are at a lower level in the hierarchy. In addition, one member of staff can be in charge of more than one studios, and so the relationship of a member of staff to studio can be represented as below: Staff to studio 1:M A manager on the other hand, can only be in-charge of one studio. Thus the relationship can be represented as below: Studio to manager 1:1 A driver can be in possession of more than one van, provided he is driving at different times. The relationship will be 1:M A driver can also serve more than one studio. The relationship between driver and studio will be 1:M The different elements are all related to make up the company which can be seen as a single entity. 4. To identify multiplicity constraints, I used the knowledge that they restrict the number of objects of one class in association with an object from another class. In other words, multiplicity constraints refer to the number of entities that can be associated with other entities. They represent the number of occurrences of one entity that may have a relationship with a single occurrence of an associated entity. The multiplicity constraints provide a representation of policies established by the company, in this case SonicDream. The most communal degree for relationships is binary and the binary relationships are given in the formats shown below: One-to-one (1:1) One-to-many (1:*) Many-to-many (*:*) In complex relationships such as the one represented in this case study, multiplicity will refer to the number or simply the range of possible occurrences of an entity type in an n-ary relationship when other values are fixed. Multiplicity is made up of either cardinality or participation types of relationships. Cardinality gives a description of the number of possible relationships for the participating entities. Participation, on the other hand, provides a determination of participation of entities in a certain relationship (Moreira et al. 2013, p.455). 5. To identify attributes, I used the knowledge that they are characteristics of entities. This is derived from the understanding that an attribute must contain a value, and cannot, therefore, be left empty. For instance, COMPANY as an entity, includes many others such as the company name, registration number, area of operation, and area of specialization. Going by the original Chen notation, representation of attributes is by ovals that are connected to the entity rectangle with a line. Each of the ovals contains the name of the attribute represented. Crow’s Foot notation on the other hand, writes the attribute in the attribute box below the entity rectangle. In some cases, an optional attribute, an attribute that does not require a value, is used. This means the optional attribute can be left empty. For example, in this case, the company only has one name, SonicDream, and there is no postal or e-mail address provided. Therefore, the attributes will not be presents in bold face in the entity box. To identify primary keys on the other hand, I used the knowledge that they are used to uniquely identify one or more attributes for each entity instance. The primary key guarantees logical access to every entity. The primary keys are mapped in tables. The identifiers are underlined in the Entity Relationship Diagram. Key attributes are equally underlined in a frequently used table structure shorthand notation. For example, in this case, it will take the format below: Company_regnumber Company_Name Other_Attributes COMPANY The primary key is company registration number which should be underlined. Bibliography MOREIRA, A., SCHÄTZ, B., GRAY, J., VALLECILLO, A., & CLARKE, P. (2013). Model-Driven Engineering Languages and Systems 16th International Conference, MODELS 2013, Miami, FL, USA, September 29 - October 4, 2013. Proceedings. Berlin, Heidelberg, Imprint: Springer. Read More