Databases and Data Management
Databases and Data Management [2A ERD]
Data modeling
- Technique to document a software system using diagrams and symbols
- Used to represent communication of data
- Entity Relationship Diagram (ERD) = graphical representation of data requirements for a database
Entity relationship (ER) data model
- Used for data modelling for use with databases ⇒ easy to discuss and explain
- Readily translated to relations/tables
- E.g.
1 - Represented by ER diagrams
- Entities = tables that hold specific information
- Singular (e.g. each row represents a student) nouns
| Student |
|---|
- = A specific object of interest
- Represented by rectangles in ERD
- An entity should appear only once in a diagram
- Relationships = the associations or interactions between entities
- Describes link between 2 entities
- Relationship between entities always operate in both directions (i.e. from entity 1 to entity 2, and from entity 2 to entity 1)
- Never connect a relationship to another relationship
Relationships: connectivity ⇒ describes the relationship classification between entities
- One to One Relationship
- Mostly used to split an entity into 2 to provide information concisely and make it more understandable
- Can either merge Entity X with Entity Y or separate them
- One to Many Relationship
- An instance of entity X may be linked to many instances of entity Y, but an instance of Y is linked to only one instance of X
- The entity at the many end (i.e. Entity Y) needs to have a foreign key (FK) referencing the primary key (PK) on the entity at the one end (i.e. Entity X)
- E.g. one student has many subjects
- Many to Many Relationship
- Entity X may be linked to many instances of entity Y and Y may be linked to many instances of X
- Problematic relationship: foreign key cannot be used. A foreign key attribute can only have a single value so it cannot handle the many references required
- ⇒ Split into 2x 1:M relationship
Entity Relationship Diagram (ERD)
- Identify all the relevant entities in a given system and determine the relationships among these entities
- An entity should appear only once in a particular diagram
- Provide a precise and appropriate name for each entity, and relationship in the diagram
- Use singular nouns
- Remove redundant or unnecessary relationships between entities
- Never connect a relationship to another relationship
Steps to create ERD
- Identify Entities
- Identify relationships between entities
- Decide connectivity of relationships
- One-to-one (1:1)
- There are options for implementation, e.g. merge passport information with student information or separate them
- One-to-Many (1:M)
- No further refinement needed
- Many-to-One (M:1)
- No further refinement needed
- E.g.
- The relationship shows that the entity at the many end (Student) needs to have a foreign key (FK) referencing the primary key (PK) on the entity at the one end (Class)
- Many-to-Many (M:M) (if required)
Cardinality and connectivity – not in syllabus
Try not to have many-to-many ⇒ atomicity (A in ACID)
⇒ break it up
subject(studentID, subject name, no. hrs, date…)
student-subject(subjectID, studentID)
- Can2 use https://www.lucidchart.com/pages to draw