Designa en webbplats som denna med WordPress.com
Kom igång

Classes to tables

The method you would use for converting classes to tables depends on what kind of database you’re using. With non-relational databases such as Cassandra and MongoDB there is no standard process that i know of. But in relational databases such as SQL I would recommend start of by creating a Entity relationship diagram(ERD). This diagram is very useful for mapping a classes into tables.

In the ER-Diagram you first want add all the classes and concepts as entities in rectangles. After you have all your entities you want to define the relationships and cardinality between the entities, these should be displayed as verbs in diamonds. The cardinality is then displayed between the entities as: 1, N or M. In order to identify the cardinality between entities in a database like the one above you need to ask yourself questions such as: ”Can a lecturer only have one student 1:1 or multiple M:1? ” and ”Is each subject linked to only one lecturer(1:1) or can one lecturer teach multiple subjects and each subject can include multiple lecturers(N:M)? After you have created all the entities and their relationships you then want to put the attributes of the entities in circles around the rectangles.

When you have completed the ER-Diagram you then want to convert all entities into into tables and define the keys. Here are some of the basic steps:

1.Convert all the Entities in the diagram to tables.

All the entities represented in the diagram should become independent tables in the database.

2.All single valued attributes of an entity is converted to a column of the table

All attributes (circles), whose value at any instance of time is unique should become columns of that table.

3.Key attribute in the ER diagram becomes the Primary key of the table.

In the diagram above, LECTURER_ID becomes the primary key of the LECTURER table because it’s the best unique identifier to identify each row. Using id’s is normaly the best identifyer because the usually contain numbers and letters instead of only strings.

4.Declare the foreign key column, if applicable.

In the diagram above, the lecturer has the relationship ”Teaches” with subjects. In order to link these two togheter in the database, you need to define a Foreign Key(FK) in one of the tables that is gonna work as reference the other entities primary key. In the above example the attribute ”SUBJECT_ID” in the lectulrer entity is the reference to the subjects entity which the lecturer takes. It’s also possible to have the ”LECTURER_ID” a Foreign key in the subjects entity as show below.

Normalization

The next step before implementing your tables into an SQL database is Normalization.

Normalization is a technique of organising the tables and columns in the database to enforces data integrity and reduce reduncancy. The process includes identifying and analysing functional dependencies for all the given relations in order identify unwanted partial and transitive dependencies. Having a normalized database is very important as it prevents issues with database modifications such as insertion, deletion, and update anomalies. The process of nomarlization contains multiple steps known as normal forms. To achieve a certain normal form all the previous ones also has to be achived as well.

The first normal form (1NF) says that all the tables must contain atomic values, ie at most one value per field. For example you can’t put two color values in one field to describe the colors of a producs. Instead you would have to use multiple rows with the same primary key to store all the colors.

The second normal form (2NF) says that there can be No Partial Dependencies. This means that every attributes which are not part of any candidate key can’t be fully functionally dependent on parts of the primary key. They need to be dependent on the whole primary key.

The third normal form (3NF) says that there can be No Transitive dependency between the non-prime attributes and the primary key. This means that attributes can’t be indirectly dependent on the primary key through other attributes. They have to be directly independent.

To get a better understanding of this process i suggest watching this guide.


Annons

Lämna en kommentar

Fyll i dina uppgifter nedan eller klicka på en ikon för att logga in:

WordPress.com-logga

Du kommenterar med ditt WordPress.com-konto. Logga ut /  Ändra )

Twitter-bild

Du kommenterar med ditt Twitter-konto. Logga ut /  Ändra )

Facebook-foto

Du kommenterar med ditt Facebook-konto. Logga ut /  Ändra )

Ansluter till %s

%d bloggare gillar detta: