StudentShare
Contact Us
Sign In / Sign Up for FREE
Search
Go to advanced search...
Free

Database Modeling and ER Diagram - Essay Example

Cite this document
Summary
The author of this essay "Database Modeling and ER Diagram" casts light on the normalization of a database which is carried out in a series of steps. It is stated that each form has its own set of conditions that need to be met for the database to be considered fully normalized…
Download full paper File format: .doc, available for editing
GRAB THE BEST PAPER91.3% of users find it useful
Database Modeling and ER Diagram
Read Text Preview

Extract of sample "Database Modeling and ER Diagram"

Database Modeling and ER Diagram: Normalization of a database is carried out in a series of steps. In this case, we will consider the first normal form, second normal form and third normal form. Each form has its own set of conditions that need to be met for the database to be considered fully normalized (Maslakowski & Tony, 2000). This database contains the following tables: Student-information and Course-information. The student-information table contains the following fields: Student id, name, phone, email, street number, street name, city, and state.

This table does not satisfy the requirements of 1NF since it contains multi-valued fields. In this table, possession of more than one phone number or email address is not allowed. It might be important to have alternative contact addresses for students in case of unexpected situations. Therefore, in order to cater for such a situation, we need to create additional tables to handle the addresses. One table named email-address will contain the following fields: id, email address, and student-id.

Likewise, another table named phone-number will be created containing the following fields: id, phone number, and student-id. Finally, another table will be street details. This table will have the following fields: id, street name, city name, state, and student id. Figure 1: ER diagram showing student-information table after normalizationFunctionStatementDrop student-information table drop TABLE Trident.student-information Create table student-informationCREATE TABLE Trident.student-information( student-Id INT NOT NULL, Name VARCHAR(45) NOT NULL, PRIMARY KEY(student-Id));Create table email-addressCREATE TABLE Trident.

email-address( id INT NOT NULL AUTO_INCREMENT, email-address VARCHAR(45) NOT NULL, student-Id INT NOT NULL, PRIMARY KEY(id));Create a relationship between email-address and student-information tables.ALTER TABLE Trident.email-addressADD FOREIGN KEY (student-Id) REFERENCES Trident. student-information (student-Id); Create table phone-numberCREATE TABLE Trident. phone-number( id INT NOT NULL AUTO_INCREMENT, phone-number VARCHAR(45) NOT NULL, student-Id INT NOT NULL, PRIMARY KEY(id)); Create a relationship between phone-numbers and student-information tables.

ALTER TABLE Trident.phone-numberADD FOREIGN KEY (student-Id) REFERENCES Trident. student-information (student-Id); Create table street-detailsCREATE TABLE Trident. street-details( id INT NOT NULL AUTO_INCREMENT, street-name VARCHAR(45) NOT NULL, city-name VARCHAR(45) NOT NULL, state VARCHAR(45) NOT NULL, student-Id INT NOT NULL, PRIMARY KEY(id)); Create a relationship between street-details and student-information tables.ALTER TABLE Trident.street-detailsADD FOREIGN KEY (student-Id) REFERENCES Trident.

student-information (student-Id); Table 1: Creation of new tables and their relationshipsThe four tables created satisfy the requirements of 1NF. They are also in 2NF since they do not contain any partial dependencies. All fields in each table are uniquely reliant on the respective primary key as 2NF requires (Buxton, 2009). The four tables also fulfill the requirements of 3Nf since all fields present depend on the primary key for their values. The next table that requires normalization is named course information.

This table contains the following fields: Course-Id, course title, and course credit. This table is already in its first normal form since it does not contain multi vamulti-valuedlued fields. This table also fulfills the requirements of 2NF since all fields in the table rely uniquely on the primary key. This table also fulfills the requirements of 3NF since each field in that table is dependent on the primary key. The last thing now is to create a relationship between a student and the courses that they take. This can be done by employing a many-to-many relationship between the student-information table and the course-information table.

In order to accomplish this relationship, another table is required. This table will contain the primary keys of both tables and an additional field named student credit. In this way, a student can take many courses and many students can take one course. Figure 2: Final ER diagramReferenceBuxton, S. (2009). Database Design: Know It All. Amsterdam: Morgan Kaufmann/Elsevier.Malinowski, M., & Tony B. (2000). Sams Teach Yourself MySQL in 21 Days. Indianapolis, IN: Sams.

Read More
Cite this document
  • APA
  • MLA
  • CHICAGO
(“Database Modeling Essay Example | Topics and Well Written Essays - 500 words”, n.d.)
Database Modeling Essay Example | Topics and Well Written Essays - 500 words. Retrieved from https://studentshare.org/information-technology/1591385-database-implementation-2
(Database Modeling Essay Example | Topics and Well Written Essays - 500 Words)
Database Modeling Essay Example | Topics and Well Written Essays - 500 Words. https://studentshare.org/information-technology/1591385-database-implementation-2.
“Database Modeling Essay Example | Topics and Well Written Essays - 500 Words”, n.d. https://studentshare.org/information-technology/1591385-database-implementation-2.
  • Cited: 0 times

CHECK THESE SAMPLES OF Database Modeling and ER Diagram

Development of an Internet Based Housing Demand Database System

The author of the following paper "Development of an Internet-Based Housing Demand database System" provides an overview of the various dimensions related to the development and evaluation of housing information systems in the online platform.... The paper focuses on the nature of systems, concepts, and theoretical aspects involved in the design and development of an effective internet-based database management system....
22 Pages (5500 words) Dissertation

EXERCISE 5 P+P

structural overview with diagram and text that is beneficial for easier implementation, better quality system and a use of improved planning.... The cognitive bias of Merise is that it is not limited only to the modeling of a database (Pham, 1991).... Running Head: 5 P+P ideas on SSADM, Merise 5 P+P ideas on SSADM, Merise 1....
4 Pages (1000 words) Research Paper

Database Logic and Management questions Module 2

One of the basic purposes of an E-R diagram is to clarify the system requirements particularly in case of a system which involves implementation of a database (Hoffer, Prescott,.... One of the basic purposes of an E-R diagram is to clarify the system requirements particularly in case of a system which involves implementation of a database (Hoffer, Prescott, & McFadden, 2007; Whitten, Bentley, & Dittman, 2000).... In this scenario, (Craven, 2001) discuss some of the major advantages and disadvantages of E-R modeling which are outlined below: AdvantagesOne of the major advantages of E-R diagram is that it simplifies the conceptual structure of a system It offers a graphical representation of a system....
2 Pages (500 words) Article

Database Schema for a Health Care Clinic

The paper also describes deleting using a marker column, database schema, and diagram which is indicated on the PowerPoint presentation.... ER- Entity Relationship diagram- This shows all the entities and their relations in a given database (Luocopoulos, 1994).... "database Schema for a Health Care Clinic" paper describes the entities and relationships of health care clinics together with their relationship.... A collection of a relation schema is called a database schema (Herman balsters, 2001)....
2 Pages (500 words) Essay

The Term Relational Database

From the paper "The Term Relational database" it is clear that flexible retrieval technique greatly aids database administrators (DBAs) in testing and verifying the integrity and content of databases.... Data in a table can be related in terms of common concepts or keys, and the ability to retrieve related information from a table is the genesis of the term relational database.... In the case of a relational database, an RDBMS (relational database management system executes data storage, maintenance, and retrieval, just like a typical database should....
4 Pages (1000 words) Coursework

Rationale Databases and Data Warehouse

The paper "Rationale Databases and Data Warehouse" highlights that the data is moved from the operational data store in the data warehouse database and right here the data are kept in a hierarchical manner and this modification is generally referred to as dimensions, specifics/total specifics.... More than anything, it is relevant to specify here that if in any respect, the company under presentation has integrated data (in place of different standalone operational database systems), the procedures before the integration layer would not be needed to execute....
5 Pages (1250 words) Essay

M4A3 Project Milstone 2: Data Modeling Layout

In this stage, use of entity relationship diagram is employed in mapping the information about the enterprise (Owen et al.... Consequently, these companies require a database to automate the process of coming up with the Data modeling Layout Insert Insert Introduction Architectural companies experience a lot of challenges when it comes to handling massive data.... ata ModelingData modeling refers to how the logical structure of a database is....
2 Pages (500 words) Coursework

Object Role Modeling (ORM)

A database management system is a suite of computer programs designed to manage large sets of structured data and is responsible for running operations from data requested by numerous clients.... There are many forms of DBMS (database management systems) which include such company areas as accounting, human resources, and customer support systems.... By mapping the relationship concept schema, database design is then produced and then a logical schema will then be produced....
40 Pages (10000 words) Term Paper
sponsored ads
We use cookies to create the best experience for you. Keep on browsing if you are OK with that, or find out how to manage cookies.
Contact Us