Logical Entity Relationship Diagram of Soundmission Company - Case Study Example

In this case study of the SUBMISSION Company, we have been given the conceptual diagram and we are required to form the logical Entity Relationship Diagram (ERD) based on the conceptual diagram. The logical data model is developed without considering the database tools to be used for the creation and implementation of the database.

The logical ER data model facilitates identifying and specify additional data entities include but not limited to the Transactional and Operational data entities. Moreover, the logical ER data model contains more information related to the relationships, primary and foreign keys, as compared to the conceptual data model. In the ER logical models, the attributes of the entities are defined, refined, and categorized in diverse attribute types include the derived attributes, composite attributes, multi-valued attributes, null and simple attributes. Furthermore, relationships are established in the logical ER data model. There are three (3) major types of relationships include one-one (1 to 1), one-many (1 to many), and many to many relationships. The relationships are based on the primary key and foreign key. The base table contains the primary key that makes a relationship with the other data type contains the foreign key. Usually, this type of relationship is said to be a ‘one-to-many’ relationship. However, the same relationship can be changed by implementing the constraint of uniqueness to the attribute / foreign key to restrict duplicate values. This implies that the established relationship is one to one because both the data entities contain the non-duplicate value (Tao, n.d).

The document presents the conversion of the conceptual data model into the logical data model for the company named SOUNDMISSION. Moreover, the document can be considered as the base document for the development of the database for the company. In the beginning, it is required to identify the relationships and operational data entities and transactions in the ER conceptual model of the company SOUNDMISSION. To determine the relationship, it is useful to determine the primary keys of the entities, the data contained in the conceptual model ER.

Data Entities, Primary Keys, and their Relationships

It is worthwhile to know the definition of the primary key defined as “a key uniquely identify the records stored in the relation/table” identifies a unique record in the table of the relational database. Moreover, the primary key cannot NULL and can be defined only one time in a table.

After the selection and identification of additional data entities, it is appropriate for the formation of relationships between data entities. In this regard, it is important to consider the ER conceptual model as a basis and starting point. Data entities that have one and only one element to establish a connection with one or more items of the already formed data entity known as ‘one-too-many’ relationships. In this case, the data entities that contain a single element reflect the primary key, while the data entities that contain multiple items reflect the foreign keys. A foreign key is a field in the table that communicates with the primary key in another table to establish a relationship. Using this standard, the following relationships must be established.

1. The data entity ‘Staff’ has a primary key named ‘staff’.
   1. The primary key of the entity data ‘Staff’ served as a foreign key in different data entities include Application, Rental, Driver, and Maintenance Equipment.
2. The data entity ‘Customer’ has a primary key named ‘custom’.
   1. The primary key of the entity customer data served as a foreign key in different data entities include: rent, Request, and hire Portable.

• The data entity ‘Engineer’ has a primary key named ‘engrNo’.
  1. The primary key of the Engineer served as a foreign key in different data entities include equipment, maintenance, Customer, Rent, and Staff / Manager.

1. The data entity ‘Equipment’ has a primary key named ‘equipCode’.
2. The data entity ‘Maintenance’ has a primary key named ‘maintenceCode’.
3. The data entity ‘Studio’ has a primary key named ‘studio’.

• The data entity ‘Request’ has a primary key named ‘requester no’.
• The data entity ‘Driver’ has a primary key named ‘licenseNo’.

1. The data entity ‘Vans’ has a primary key named ‘vanRegNo’.
2. The data entity ‘Portable hire’ has a primary key named ‘hireagreementNb’.

The relationships established by all the vital data entities have been discussed above. However, there are a few data entities that establish one or two relationships and these data entities include the staff, van, manager, and studio.

Logical Entity Relationship Diagram

            Structured Query Language (SQL)

• The following query would be utilized to create the table of ‘Vans’ and its respective attributes

CREATE TABLE vans ( vanRegNo int NOT NULL PRIMARY KEY, make varchar ( 255 ), series varchar ( 255 ), loadCapacity int, milage int, regulate date

licenseNo int FOREIGN KEY REFERENCES driver ( license ) ) ;

• The following query would be utilized to create the table of ‘Staff’ and its respective attributes

CREATE TABLE staff ( staff int NOT NULL PRIMARY KEY, staff name varchar ( 255 ), staff position varchar ( 255 ), staffHireDate date, staff salary int, staff email varchar ( 255 ), staffMobNo int, ) ;

• The following query would be utilized to retrieve all details of either Mercedes Vans registered after 01 / 01 / 2010 or all the vans having mileage less than 10000

Select * from vans where ( series = “ Mercedes “ and date > ‘ 01 / 01 / 2010 ‘ ) or mileage < 10000

Read More