Normalisation & ER Diagrams help!Watch
Hello everyone i am really struggling with both normalisation and ER diagrams for ICT and they can end up giving you quite a lot of marks within the exam. I am doing the A2 ICT exam with the CCEA exam board. If anyone could help me that would be really appreciated
With regards to ER diagrams, what parts are you struggling with?
"An entity relationship diagram (ERD) is a graphical representation of entities and their relationships to each other, typically used for modeling the organization of data within databases or information systems."
In this example, we have 6 entities containing information regarding bus route management.
- The primary key (indicated by a key) uniquely identifies a record in a database. E.g. each driver has their own ID number, this makes them unique in a database.
- The foreign key (indicated by the green arrow) uniquely identifies a record of another table in the same table, think of it like a reference to another primary key. E.g. each schedule has its own route, which is identified by a route ID in the route table.
- varchar, integer, float, date refers to the datatype for each column, the number in the bracket defines the character length.
- N refers to columns that can contain a NULL value.
Relationships...There are several to take note of here.
- One-to-one relationship: Between bus and schedule. Both tables can have only one record on either side of the relationship. Each bus has its own schedule and each schedule is used by a single bus.
- One-to-many relationship: Between route and schedule. The primary key table contains only one record that relates to none, one, or many records in the related table. A single route may have 1 or more schedules and 1 or more schedules are run by a single route.
- Many-to-many relationship: Between route and stop. Each record in both tables can relate to any number of records (or no records) in the other table. A route may have 1 stop or many stops, at the same time these stops may be present in 1 route or multiple routes. Notice how a third entity is created in a many-to-many relationship, this is known as a linking table or an associative entity.