file-based database design

Assessment 1_602/REPORT_ASSESSMENT_1.docx

ASSESSMENT 1 MIS604

DATABASE MODELLING AND DATABASE DESIGNING

REPORT ON DATABASE OF THE TORRENS UNIVERSITY AUSTRALIA

Data is known as the facts or information that we are using every day for carrying our services. Whereas database is the collection of those facts or information and is found in an appropriate organized manner. Now when we are arranging our data or facts into a form of tables with rows and columns this process is known as the data modelling.

So, first of all, when I have started collecting the data about Torrens University, I have first draw ERD diagram, the major reason behind this was to get an idea about the relationship between the objects and their attributes. Thus, I have found that there are 4 main entities in this database, the University, Student, Course and Lecturer. Similarly, there are several attributes linked to these mentioned entities, such as, Student id, First name, Last name, Course Id, Course name, Lecturer name and so on. This is also known as a data modelling. Thus, drawing a diagram was an easy task, however, finding a relationship between them was a little tricky.

So, I have used crow’s notation to demonstrate their relationship and have used the bridge as well. Further up, now it is a time to put all the data into the table form into the excel spreadsheet. When I have started, I found it easy to distribute the data into rows and columns. I have used three different tables for each entity, student, course and lecturer.

The major reasoning using three different tables to avoid the mixing of data and further complexities. I have used, student Id, course id and lecturer Id as the primary keys to demonstrate the value of the all other related attributes. Also, there is a composite key that I have used to prevent data anomalies, which is a combination of Student ID and First Name. Moving further, in version one and two everything was going smoothly, I have deleted two rows easily followed by the addition of the 5 new rows, under those same attributes. So far, I did not face much issue.

However, the problem comes up when I moved to version 3, where I have to delete the two existing data rows and have to add two new attributes. An insertion and deletion anomalies occurred that leads to the data inconsistency. The reason is, student has got relationship with the course in which they have enrolled and with the lecturer who is teaching them. So, if we are deleting something in student table then it automatically got deleted from the course table and then from lecturer table as well. Likewise, while adding a new information into the student table, we have to update into another two tables as well.

Therefore, I preferred to apply the concept of normalization of the data to avoid any data inconsistency. This makes the database less complex and more informative.

Hence, the major part that I have done is I have further split the table using composite key, where I have easily added two new attributes without affecting data in the rows in the primary table. I have tried another way as well where I have just simply added two more columns with new attributes, but I have faced hurdle, as while I was deleting the existing rows, information in those table also got deleted and vice versa, creates a deletion anomaly. Therefore, to prevent this I have created two separate new tables with composite key, where I can easily add, update, insert and delete the data without affecting other tables.

Finally, reaching at fourth version, where, I have to delete the five existing attributes. I have deleted three attributes from student table, each one from the course and lecturer table respectively. Lastly, I have added five new rows with the data.

Overall, it was good experience, I got to know the concept of relationship within the entities and attributes. I have practically applied the normalization concept, found the anomalies within the database and taken the best possible step, by separating the table using composite key. I have also used color highlights to show the changes that I have made in the tables.