Database Design and ERD Creation: A Comprehensive Guide for Students, Essays (university) of Principles of Database Management

Download Database Design and ERD Creation: A Comprehensive Guide for Students and more Essays (university) Principles of Database Management in PDF only on Docsity!

Database Design and ERD Creation

DBM/380/Week 2

17 May 2018

Professor John Von Tungeln

Database Design and ERD Creation

The IT consultant-client sat with us to give us the specifics of what they are looking for

in a database. Primarily they want to track the names of their consulting staff, with each staff

member's skill sets, and what projects they are working with. We required a list of all employee,

a list of every skill and skill description, project names, and all pertinent information related to

the data. The next step was to formulate a logical design or LD. The LD is the conceptual model

that will start to organize what they want into what our database managers can and cannot do, see

Figure 1. What constraints are there, i.e., budget, time deadlines, etc.? What will the reports

visual effect be? Is the current system antiquated with usable data? Who will use the database?

What database manager language will be used, i.e. SQL, Microsoft Access, etc.? System design

and constraints are the first order to discuss.

Figure 1. Illustrates an example of a portion of a logical design information used to begin

building the ERD. This information is provided from the client which will be used by database

specialists.

Employee Skills Skill Description Project Project Description

Attribute 1: Employee ID Attribute 1: Skill

ID

Attribute 1: Skill

Description ID

Attribute 1:

Project ID

Attribute 1: Project

Description ID

Attribute 2: First Name Attribute 2:

Employee ID

Attribute 2: Skill

ID

Attribute 2:

Project Title

Attribute 2: Employee ID

Attribute 3: Last Name Attribute 2: Skill

Code

Attribute 3:

Employee ID

Attribute 3:

Project Start

Attribute 3: Project ID

display each member's skill and its description. Employee human resource information can be

displayed. Complete project information or skill description, all can be utilized by department

heads and managers to organize and control consulting projects.

Next step in this phase to consider and manage data integrity. Every entity, table, column

heading, every field, every value, must be entered accurately and updates must follow the

database rules and policies. At this point, the Primary Keys or PK and Foreign Keys or FK will

need to be established so tables have a consistent relationship and can be managed relatively

meaningful so straightforward data and reports can be produced. We created the following

Primary Keys as EMP_ID for the Employee table, SKL_ID for the Staff Skills table,

SKL_DESC for the Skill Description table, PROJ_ID for the Project table, and PROJ_DESC for

the Project Description table. The following will be the Foreign Keys: EMP_ID for Employee

ID, SKL_ID for Skill ID, and PROJ_DESC for Project Description. These table and rules

support the database design requirements.

Figure 2 displays the ERD for the consulting staff member, skill, and project tracking database.

Conclusion

References

Coronel, C., Morris, S. (2017). Database systems: Design, implementation, &

management. (12th ed.). Cengage. Retrieved from

https://phoenix.vitalsource.com/#/books/9781305886841/cfi/472!/4/4@0.00:0.

Kononow, P. (2012, April). ER Diagram vs Data Dictionary – Which is Better for

Documenting Data Models. dataedo.com. Retrieved from

https://dataedo.com/blog/er-diagram-vs-data-dictionary-documenting-data-models

DBM/380 - DB Design and ERD Creation - B Joell The following assignment is based on the database environment proposed in the Week 1 Individual Assignment. Perform a logical design on the proposed database environment without going through the full normalization process. Include the identification of all entities and attributes associated with the environment. Write a 2-3 page essay to document your logical design including the process you went through. Discuss the process you went through to select the appropriate data types, primary and foreign keys, and other constraints that are necessary to maintain the integrity of the database. Create a detailed ERD using the data specifications from the logical design. Use Microsoft® Visio® or another tool to create this diagram. One alternative is to create a free account to access LucidChart, a Visio compaible tool (www.lucidchart.com). Include the ERD in the body of the essay as an APA-formatted figure. Format the assignment according to APA guidelines. Submit your assignment to the assignments area. Content (^) Exceede d Averag e Below Average Not 70 Percent Met Comments An appropriate logical design was done. X Good start on the design. Although there are several items to be corrected, it looks like you have a basic understanding of the concepts.

Entities and attributes were properly defined. X Employees and Projects are good base entities. Your third base entity should be Skills, with Skill ID and Skill Description as the starting attributes. You would then need associative entities such as Staff Skills (which you have), and Project Assignments (missing). Project Assignments would allow you assign Employees to Projects. Data types for each attribute were properly defined. X Outside of mentioning that the employee ID was numeric, data types were not discussed or shown on the ERD. Primary and foreign keys were properly defined. X Take out FKs from entities where there isn't actually a relationship, such as EMP_ID in STAFF_SKILL_DESCRIPTION and PROJECT_DESCRIPTION. Also An appropriate ERD was submitted. X Good start on the ERD itself, and good use of Crow's Foot notation. In future weeks, space the entities and lines out more so that the diagram is easier to read. Total Availabl e Total Earned 7. (^00) 5. Writing Guidelines (^) Exceede d Averag e Below Average Not 30 Percent^ Met^ Comments Intellectual property is recognized with in- text citations and a reference page. X Good The essay is formatted according to APA guidelines. X Good Sentences are complete, clear, and concise. X Good Rules of grammar and usage are followed including spelling and punctuation. X Good The essay had an appropriate introduction and conclusion. X Good