Relational Database Development: ERD Diagram and Data Normalization, Essays (university) of Computer Networks

Download Relational Database Development: ERD Diagram and Data Normalization and more Essays (university) Computer Networks in PDF only on Docsity!

Relational Database Development

Name: Allan D’souza

Lecturer: Kevin Collins

Project plan

Contents Start date End date

Erd design 18/11/21 24/11/

Unf 18/11/21 24/11/

1nF 24/11/21 27/11/

2nf 24/11/21 27/11/

3nf 24/11/21 27/11/

Coding 30/11/21 03/12/

review 03/11/21 05/12/

Evaluation 19/01/22 24/01/

ERD Diagram

3NF

 Student: studentid, sfname, ssurname dateofbirth,^ addressid, resultid, nationalityid.  Course: courseid, courseduration, courseleader, coursename  Staff: staffid, sfirstname, ssurname,^ addressid,^ resultid, courseid  Tutor: tutorid, Tfirstname, Tsurname, subjectid*  Subject: subjectid, subjectname, subjecttutor, courseid,tutorid, staffid*  Address: addressid, postcodeid, streetid, cityid, countyid  Streetname: streenametid, streetname  Postcode: postcodeid, postcode  County: countyid, countyname  City: cityid, city  Nationality: nationalityid, nationality  Venue: venueid, location, venuename  Result: resultid, grade  Studentcourse: studentid, courseid  Coursevenue: courseid, venueid

Data Dictionary

T

Improvement of feedback

Troy: I have made the change to 3NF by making a separate field for many-to-many relationships. Jonny : Thanks for spotting the error I have inputted it in now. Joel : Yes, thanks for the feedback I have added the values in the data dictionary now.

Queries

Test Plan

Test Purpose Test Data What is Expected

Student

Details

To check if the

student inputted

data works.

“113394, Allan,

D’souza, 30-08-

Student ID, student name,

student surname and dob

should displayed.

Street To test if street

name comes up

I did not entry

anything for

street name

It should not accept it.

Results To test if grade

display none

“D*DD” It should display the grade

Tutor To test if

repetited subject

ID works

“3742” It should display it as a tutor

can have same subjectid.

Course To test if the

repetited course

“2 years” I will accept it as I have there

can be many courses with

Course To test if the

repetited

course duration

works.

“2 years” I will accept it

as I have

there can be

many courses

with duration

of 2 years

As expected

Venue To check if the

venue location

accepts the

location for

multiply times.

“Kings

road”

It will accept

the same

location as I

have made it

default.

As expected

Testing outcome

Test 1: Test 2: TEST 3:

TEST 4: TEST 5: TEST 6:

Input data Form

I have created a student data input form from Apex Oracle App builder. Here the student can enter their Name, Surname, Date of birth, Course Name, Address, phone number and Email address. I have made it like an enrolment form. Then, I decided to change the colour theme to black for accessibility feature as black colour best suited for the human eye and I will help the users during the night too. I have created a course input form for the student using Apex oracle App builder. Here I have listed all the important fields of the course, here the student can Input the data.

Notepad Validation Code

name, and the name of the course's instructor. I also added personnel and filled in their full names, roles, and addresses, among other things. Then I linked Subject to Course, Staff, and Tutor, demonstrating how they are all connected. UNF (un-normalised form): To begin, I created an insert data section for the student: I created an enrolment form where the student must provide their full name, nationality, address, and birth date. Second, I created a course table in which I included the course name, duration, course leader, number of students, and number of subjects covered in the course. Then I created a table for each topic, filling in the subject name and tutor. I have created a section for employees, in which I have put all the personnel's information, such as their name, job title, and address. Finally, I added the venue field, which contains the postcode, venue name, and location. I added the same columns to the first normalised form (1NF), but now the normalisation process begins by deleting duplicate groups from individual tables, creating a new table for each collection of related data, and using a primary key to represent each group of related data. I added a student ID, full name, student home number, street name, city, county, date of birth, and result ID* to the student field. I chose "" since it is a foreign key. Then I added a county field for the student to enter their county, followed by a postcode field for the student to enter their postcode. Then I established a field for a course that contains all the course's information, such as the course name, course leader, and course duration. Then, for the employees, I included a field for personal information such as full name, address, and nationality. In addition, I built a separate table for each venue, where I entered the name and location of the venue. In addition, I have a field where I can save all their information in the database. In the second normalised form (2NF) Here if there are partial dependencies also on the primary key, transfer those to a separate connection with duplication of its determinants and there is reduced redundancy present, but it can occur sometimes. For (2NF) I have copied the same fields in, but I have added foreign keys like student id, course id* and address id. I have used the asterisk sign “” to define the foreign key. In 2NF I have made single table for street, postcode, county and housenumber which I then changed in 3NF by keeping the field but added another field for address which make it more suitable than those single table. In the third normalised form (3NF): This is the final stage in eliminating redundancy completely. I first copied the field from 2nf, but now I have added foreign keys to the individual fields. For example, the student had a primary key of student name and date of birth, but now I have added address ID, result ID, and nationality ID for the student field. Additionally, I have created an individual field for the result, address, postcode, county, city, nationality, and subject. This implies that if I need to add a student's address, I must use "" as a foreign key. lastly, all the many to many fields, such as Student course: student ID and Course venue: course ID venue ID*, are connected.

In the data dictionary , I imported all the data from 3NF, but I also created additional tables, such as Validation necessary, Allowed null, Primary key, and foreign key. This will make it easy to code the information in SQL since I will not have to waste time later deleting tables because the data dictionary has all the information. I began by constructing a student table, which contains the following fields: student ID, student name, student surname, student date of birth, student country, student outcome, and student address. I also constructed a course table and filled it with information such as course name, course id, course name, course duration, and course leader. In addition, I constructed a staff table with entries for the employee's name, address, and course ID. Similarly, I established a database for a tutor, topic, and address, then constructed tables for the city, county, street, and postcode, and finally connected them in address. Finally, I created a table with the venue and results. I believe that my assignment that was based on a small college in Berkshire was good as I have met the requirements that were given to me. Things that as good were good about my assignment was the tables that I created in SQL which were detailed and where all the data that I had inputted was accurate and the fields that I created for the postcode, street name, house number was incredibly good then, I merge those field in one field called address. Moving on I created a table for students where the student will get their student ID, student name, their date of birth, course name, address, and results in the form of grades ranging from Grade PPP-DDD*. All the information of the student was correctly displayed without any errors all the validation techniques that I used help to prevent the database from having any errors. Validation rules have been proposed to enable only particular values to be input in a field, record, or both. Validation rules aid in the elimination of database errors; for example, I used "dob date" to ensure that the inserted data is in a Date format. In addition, I utilised Not Null validation criteria to see if the information was entered correctly. If the data is not entered, an error notice will appear on the screen, instructing the user to input the data into the table. Change that I made during the development process are the field name “street name” field had some errors, so I had to use validation techniques to get over this problem I used “Not Null” validation to makes sure that data is entered, and if the data is not entered it will prompt the user to try again. This technique helps to prevent errors from occurring and this helps make the database more robust. Lastly, I converted all the fields to SQL. I began by creating child fields such as the county, street, postcode, house number and city. Moving on I began to create parent fields such as student, course, and staff and added a foreign key to the fields like address id, nationality id,