Modelling The NorthChem Database: Essay On Entity Relation Diagram, Querying, And Design Considerations.

Entity Relationship Diagram for NorthChem

Part 1

This part is based on the ‘NorthChem’ chemical trading company scenario

(A) Using a database design approach of your choice, produce a logical design for the database to support the information system, which is needed at the NorthChem.

Your answer must consist of ONE of the following:

• An entity-relationship (ER) diagram (20 marks) and its mapping into a set of relations (10 marks). The ER diagram should show all relevant entity types, relationship types, attributes, keys, and structural constraints. Note that not all keys are identified/mentioned in the scenario/partial data dictionary, so you are required to identify/devise appropriate keys for all the entity types. As part of the mapping process, for each relation, you should identify appropriate primary keys as well as foreign keys (if applicable). Furthermore, you need to make sure your relations obtained from mapping your ER diagram are in 3rd normal form. Your ER diagram must not show any foreign keys.

• A set of normalised relations (10 marks) obtained through normalisation process (20 marks) instead of ER modelling. You should make clear how the normalisation process has been carried out, and the reasoning employed, in particular quoting evidence (series of steps) to support the decisions made and how various relations have been derived. Each relation in your answer should be in 3rd normal form.

(B) Based on your logical design from Part 1 (A) and the information available in the scenario, produce an SQL script file using Oracle 11g/12c.

Part 2

This part is based on your answer / solution to Part 1, i.e., design and implementation of the database for the ‘NorthChem’ scenario.

(A) Populate the database with some data (e.g., by generating input data based on sample data included in the scenario

(B) Answer the following queries (retrievals) using SQL and relational algebra.

1) Display names of representatives, details of the products they represent, and names of their supervisors.

2) Display details of customers together with details of their areas and names of the managers of their areas

Part 3

(A) Consider the NorthChem scenario in the Appendix. Produce a report for the managing director of the company elaborating on the professional, legal and ethical issues that need to be considered and make any recommendations that you think are appropriate for development and management of the database for NorthChem.

(B) Compare and contrast different approaches to database design (e.g., entity relationship modelling, normalisation, etc.) and briefly justify the approach you have used for answering Part 1 (A)

This report discusses the modelling process of the NorthChem database which is a company that deals with selling of products in small quantities to its customers in different area through sales representatives.

This section shows the entity relation diagram for NorthChem based on the case study provided.

 ERD

Figure 1: Entity relationship diagram

ERD Explanation

The entity relationship diagram shows the relations derived from the case study. It shows every relation with all its attributes. Because this is a relational database, every relation is related to another relation and the ERD shows the relationships and their cardinalities.

This section discusses data insertion in the database and running of queries to get data.

Query 1

select r.name,p.*,r2.name as "supervisor" from reps r

inner join productreps pr on pr.repID=r.salesrepid

inner join product p on p.productno=pr.productno

inner join reps r2 on r2.salesrepid=r.salesrepid;

Relational algebra

Reps n productreps n product n reps

Query 2

select c.*,a.*,r.name FROM customer c

inner join area a on a.areanumber=c.areanumber

inner join reps r on r.salesrepid=a.manager

Relational algebra

Customer n area n reps

2 or 3 lines

Report 1

NorthChem wants to implement and deploy a new database management system that will be used to manage its stock of inventory items, customers and its sales representatives. The database management system will be implemented to help with the process of recording data and saving data while at the same time simplifying the process of retrieving the data.

Implementing this new information system will have professional issues that should be adhered to during the development of the management system. The development team should ensure that the system is developed professionally by following all the software development principles so that the end product is professional.

Implementation of the proposed information system will also will also have to adhere to all legal issues. There are various legal issues that the development team and the organisation must adhere to. The organisation can get a system auditor to audit the system to determine whether or not the system has followed all legal issues. However following of legal issues can be a problem can be a problem for software development because software development can be done from different countries unlike other industries like production (Bell, 2013). This can be attributed to the fact that the most countries have different legal issues from other countries thus the developing team should consider all the regulations issues of the origin country of the client. But this can also be a problem because the software can be implemented in different countries in the case where the company has different branches thus the developing team should ensure that all the legal issues are followed for all the countries. Sometimes this will require deployment of different versions of the same program working together.

Data Insertion and Querying

There are also ethical issues that must be considered with the development of the system. This issues should be followed by both the development and the organization itself. The development team should make sure all ethical issues are adhered to during the development of the system. For example, the development process of the system should empower the each member of the development team. The development team should put in place measure to make sure that customer data is safe from malicious users.

During the development of the NorthChem database I would give a recommendation to the development team to follow all design principles to make sure that the final design of the database adheres to all principles. The design should ensure there is data integrity and consistency in the database. By ensuring data consistency and integrity, the huge volumes of data that will have added up in years will be used to construct a data warehouse which can be used to facilitate the decision making process. The deployment of the database should also be done on a powerful database server which should be maintained and upgraded regularly because the database will grow in size with time. The database administrator tasked with maintaining the database should ensure that regular backups of the database are made. The backups will be useful in case the primary database fails thus the database administrator can use to the backups to restore the database.

Report 2

The aim of any database design process is to create a database that will store data and maintain data effectively. The end result of the design process of a database is an acceptable logical data model. To effectively understand the similarities and differences that exist between normalization and entity relationship modelling, there is need to understand the phases that database design goes through. The four phases that database design goes through are;

• Requirements analysis phase
• Conceptual design phase
• Logical design phase
• Physical design phase.

Entity relationship modelling is also known as top-down approach. This approach of database design focuses on higher-level constructs including collections of entity types, identification of populations and the relationships that exist between the identified populations. The design process using entity relationship diagram starts with a set of higher level requirements for example narratives. From the narrative a process of identification of the types of entities that are needed for data representation as well as the attributes of the entities is done. The attributes identified will later on become attributes in the database tables (Kung and Gardiner, 2012). To identify the entities, the database designer tries to develop conceptual models through identification of highly abstracted objects or entities that may exist within the problem domain. This process uses various techniques for example making of observations, conducting interviews and questionnaires, case study review and other requirements gathering techniques. The data model also gets a lot of its inspiration from a thorough analysis of the business rules. Apart from the identification of structural properties for example relationships existing between entities, the cardinalities of relationships is also identified. Early stages of bottom up approach including drafting of an initial data model that does not have any attributes but only the entities mapped to each other to show the relationships existing between the entities. After achieving a satisfactory data model the database designer shifts his focus on the platform on which the database will be deployed. This stage deals with the development of the logical schema and requires the database designer to put into consideration any mapping issues between the structures of the ER model and the chosen persistent mechanism i.e. (relational or object-relational).

In contrast normalization which is commonly known as bottom-up approach considers designing of a database as proceeding from initially analysing lower-level conceptual units for example functional dependencies and attributes and then moving towards a more acceptable logical data model by grouping associated attributes logically (Muhammad, 2012). Normalization approach views the task of population identification as a process of object identify generalization from structural dependencies examples. For bottom-approach inputs required include views of data for example reports or screenshots. Normalization addresses potential deficiencies in a design of a relational schema that are associated with different levels of normal form. Normalizing helps reduce dependency and redundancy

Conclusion

Entity relationship modelling and normalization have their own advantages and disadvantages but are better when used interchangeably.

References

Bell, F. (2013). 10 WHATEVER HAPPENED TO INFORMATION SYSTEMS ETHICS? Caught between the Devil and the Deep Blue Sea. Information systems, [online] 2, pp.23-30. Available at: https://link.springer.com/content/pdf/10.1007/1-4020-8095-6_10.pdf [Accessed 8 May 2018].

Kung, H. and Gardiner, A. (2012). Comparing Top - down with Bottom - up Approaches: Teaching Data Modeling. Information systems, [online] 29(1910). Available at: https://pdfs.semanticscholar.org/3175/84283d177f704b41c0256847888b1b95f47b.pdf [Accessed 7 May 2018].

Muhammad, M. (2014). What is the differences between conceptual and logical database design?. [online] bayt. Available at: https://www.bayt.com/en/specialties/q/90153/what-is-the-differences-between-conceptual-and-logical-database-design/ [Accessed 8 May 2018].