Models of Data storage and its evolutions

Table of content

Table of content

Chapter 0 Introduction

Chap 1 Models of Data storage and its evolutions

I-Flat file system

II-hierarchical model

III-Network model

IV-Relational model

IV-a-Properties of a relational database management system (RDBMS)

V-partial conclusion

Chap 2 Database modelling and design(ER diagram, naming conventions and design)

I-Database design approaches

I-a-Top-down approach

I-b-Bottom-up approach

II-Business process and rules

III-understanding the entity-relationship model

III-a-Entity

Chapter 3 RDBMS and implementation

Summary

Chapter 0 Introduction

Databases are at the hearth of any information system architecture of any business nowadays. By helping to manage collected information, databases are now seen as a decisional tool in companies and by implementing, they can carry out important changes in business management. Those changes vary from business to business. For an online-selling company for instance, it can be used to know the habits of their customers in order to provide them with the best products or the ones they are more likely to buy. By doing so, it will reduce the time made by each customer online and as a benefit for the company, it will reduce the traffic to the web server and easily handle more online customers.

For our case study, which is related to the management of the students at LSC group, such a database will effectively enhance the way student’s data are stored and exploited. My report will then have these objectives to provide with up to date and details information about database design for LSC group. In order to achieve that goal, we will go through different parts and in a first time, I will make a comparison between various data storage models such as hierarchical model, network model and relational model which will lead to the choice of a model to use. Then will follow a study of two different databases design approach which is top-down approach and bottom-up approach. Before concluding, we will design our database and provide the business rules and different naming convention of it.

Chap 1 Models of Data storage and its evolutions

Databases have known a long evolution which is spread over the last 40 years now. That evolution goes from flat file system to object-oriented database with more features, flexibilities and complexities. In this first chapter, we will focus on different database models mainly the hierarchical model, the network model and the relational model.

1. Flat file system

Flat file system is the earless data storage method. Data where simply entered in a file. All the tables were stored in a single file without any relationship between them. That model gave huge work to the programs and programmers who wanted to access stored data because there was no standard among different company who handle the file. So a written program was useful for a single type of file and not with the other one. Moreover, that storage system does not have a process of checking or to avoid redundancy, so data was hugely duplicated. Those limitations lead to an improvement in data storage which is the hierarchical model.

2. hierarchical model

Hierarchical database model is a model that uses a one-to-many relationship between its elements or tables. Hierarchical model is also well defined as an upside-down tree with the one side or 'parent' at the top of it and children at the bottom, each child can be a parent as well.

Figure

Compared to the file-based system, hierarchical model had a lot more of advantages in term of data structure which is mainly relationship between tables. Lets us consider as example our case study to clearly explain it. The hierarchical model will enable a semester, which will play the role of the one side, to have many courses, which are children here. We can then continue with a course that has many subjects.

“Figure”

By doing so, data are easily managed and it is easy to retrieve data from the database.

However, that model has also its limitations. We saw previously that a course have many subjects and according to the hierarchical model, a subject cannot be taught in different courses. Generally speaking, it means that a child cannot have more than one child and that is a limitation in real live because a child can have a genital parent and an adoptive one who are both its parents. This then introduce the many-to-many relationship which will be improved in other database model. Another limitation with that model is data duplication that mostly occurs in the many or child side.

3. Network model

Network model improves hierarchical model by adding many to many relationship among elements. This is made by the creation of link between records. A record here is a couple of attributes, each of them holding only one data value. So records are seen here as entity compared to relational model and links as relations. By linking two records, the whole will make a set.

One of the main problems with network model is the number of link established among records which increase the number of sets. For instance, let assume that a student can take many subject and a subject can be taken by more than one student. They will be a need to explicitly create for each student's record all the links to the subjects he is taking and vice-versa.

Figure.

This effectively creates many-to-many relationship among records but it also make the number of link and sets to manage increase as the number of record increases.

4. Relational model

Relational model adds more features to the previous models. Instead of having each record stored and managed separately like in the hierarchical model, data are organised in tables. Each table containing attributes of different types. Each of these attributes storing more than one data value. Data in a table are organised in rows which make a record.

Fig showing a table, its attributes and the records.

Relations are established by linking records of different tables via a specific attribute known as the primary key. By doing so, we can easily represent different types of relation among tables such as one-to-one, one-to-many or many-to-many relationship.

IV-a-Properties of a relational database management system (RDBMS)

In order to consistently store and manage data, RDBMS has some properties or rules to follow. According to Stephen and Plews (2001) P 50-51^[1], they define the following properties of a RDMS

“

• A relational database management system must be able to manage databases entirely through its relational capabilities.
• All information in a relational database (including table and column names) is represented explicitly as a value in tabular format.
• Every value in a relational database is guaranteed to be accessible by using a combination of the table name, primary key value, and column name. This means that you need not know the physical location of the data, and that you can directly access any row of data in a database table independently.
• The DBMS should provide support for the treatment of null values (unknown or inapplicable data), which are distinct from default values, and independent of any domain.
• The description of the database and its contents is represented at the logical level in tabular format and can therefore be queried using the database language. This refers to metadata that is stored in the data in regards to the database itself. Metadata is data about other data.
• At least one supported language must have a well-defined syntax and be comprehensive. It must support data definition, manipulation, integrity rules, authorization, and transactions.
• All views that are theoretically updateable can be updated through the system.
• The DBMS supports set-level retrievals, inserts, updates, and deletes.
• Application programs and ad hoc queries are logically unaffected when physical access methods or storage structures are altered.
• Application programs and ad hoc queries are logically affected as little as possible when changes are made to the table structures. Although, if a table is split into two tables, the application will have to be changed so that the appropriate columns are accessed from both tables.
• The database language must be capable of defining integrity rules to protect the data. These rules must be stored in the online catalog, and cannot be bypassed when data is modified.
• Application programs and ad hoc queries are logically unaffected when data is first distributed or when it is redistributed. Distribution refers to how data is stored on hardware devices.
• It must be possible to bypass the integrity rules defined through the database language by using lower-level languages.

“

5. partial conclusion

Getting at the end of our chapter where we had talk about different database models, we have seen that there were a lot of improvements made from the hierarchical model to the relational model. These changes and improvement are recapitulated in the following table.

Table recap.

According to the above table, I will choose the relational model to design and built the LSC group database.

Chap 2 Database modelling and design(ER diagram, naming conventions and design)

1. Database design approaches

There are mainly two approaches that govern any database design. Top-down and bottom-up approach. Let's have a look at each of them and its characteristics.

I-a-Top-down approach

The top-down approach intends to look at things in general and go down to reach the more specific ones. Basically, we start by establishing all the things that the system will do like the transaction between the different actual entities of the real world. Then by working closely with the end-users, the analyst can determine what is needed to be stored in the database.

Let's show how this approach could be used in the case of a database design for a college. Firstly, the analyst will make a list of all the elements that make off the college to exist. I mean the school management team, the different departments, the teachers, the students, the courses offered by the college and their respective subjects. Then we can move to establish the responsibility of each of them which can lead to the relation that will appear in the end system. Finally, the analyst can interview some of these entities to have more details about their roles.

I-b-Bottom-up approach

Bottom-up approaches view database design as going from an initial analysis of lower-level conceptual units, such as attributes, and then moving towards an acceptable logical data model through logical groupings of associated attributes. To stay with the same case of the case of a database design for a college, the analyst will start by recording all the different data units that are managed by all the entities in the college such as the name, date of birth age, sex and address of the whole staff and students. Then, each related data will be grouped to for entities.

Our case study is already explicit in term of data collection. We already know the entities that need to be stored and even more some of their details. We also have a better understanding of the relation among those entities telling us the data flow generated. This eases our work since we don’t have to interview the end-user about their need. So for that step, we don’t need to use any database approach to determine the entities, their attributes and the relation existing among them.

2. Business process and rules

The business process and rules will help us to clearly define role of each entity and the relation that exist among those entities.

Considering our given case study, we can establish the following business process and rules:

• every student enrolled at the college shall have a name, a unique identification, a date that he start the course and a phone number;
• each course offered by LSC group has an identification, a name, a duration and a description
• it exist several semester and term, each of them having a duration and a name
• There is different subject delivered at the college. Each of them having an identifier, a name, a result, the attendance and the assessments.

Moreover, each student should be enrolled in a course to be considered as student of the college. A students can also be enrolled in more than one course but not taking them at the same time. Each student should take all the subjects related to its course. Then, it becomes necessary to store data about the results of the assessments (assignments and exams) done by every student, their attendance to every subject which will enable to determine the attendance for the whole course. Now about the terms and semesters, it is possible to have more than one semester in each term but not the opposite. For each course they will take place in many semesters.

With the above business model, it is now possible to design our database which is basically the conversion of the business model into tables and relations.

3. understanding the entity-relationship model

In the first chapter, after reviewing the different type and evolution of databases, we concluded by saying that we will use the relational model to design our database. However, before making the design, it is important to define the elements that made off the designated model.

III-a-Entity

Before designing our database, it is very important to clearly define

Conceptual, logical and physical database design

database development life cycle

Chapter 3 RDBMS and implementation

Conclusion

^[1] Stephens and Stew (2001), Database design, Sams.