WEEK 9 - DATABASES AND ENTITY RELATIONSHIP DIAGRAMS (ERD)

Question 1

Purpose of data modeling

Answer 1

• ensure the business database operates correctly and efficiently
• involves creating a data model, which serves as a “blueprint” for building the database

Question 2

define logical design in data modeling

Answer 2

models the database from a business perspective, focusing on the relationships and data flow

Question 3

define physical design in data modeling

Answer 3

focuses on how data is organized and stored on physical storage media like hard disks

Question 4

what is a data model

Answer 4

• a diagram representing main items of interest (entities) and their relationships
• entity-relationship diagrams (ERDs) are used for visualizing these models
• common notation: Crow’s foot notation

Question 5

define entities

Answer 5

• items of interest in the database, usually nouns (eg. students, courses)
• represented as rectangles

Question 6

define attributes

Answer 6

• characteristics or properties of an entity (eg. student_name, student_address)
• unique attributes form the primary key of the entity

Question 7

define records

Answer 7

• groups of related fields, representing an instance of an entity (eg. a row in a table)

Question 8

define fields

Answer 8

• individual pieces of data (eg. a single cell in a table)

Question 9

define foreign key

Answer 9

• a field that links tables by referencing the primary key in another table

Question 10

define relationships

Answer 10

• connections between entities, defined by business rules
• represented as lines connecting entities in the ERD

Question 11

define cardinality

Answer 11

• defines the number of entities in a relationship (minimum and maximum)
• notation: single line (mandatory one), crow’s foot (many) and zero (optional)

Question 12

example of ERD components

Answer 12

• entities: represented by rectangles (eg. lecturer, course)
• attributes: characteristics of entities (eg. lecture ID, course name)
• relationships: lines connecting entities with cardinality notation
  example:
• lecturer teaches courses
• one-to-many relationship: a lecturer can teach many courses, but a course is taught by one lecturer

Question 13

steps in building an ERD

Answer 13

1. identify entities and relationships
2. create a preliminary data model (without detailed attributes or data types)
3. iterate and refine the model based on feedback and analysis

Question 14

example scenarios of building an ERD

Answer 14

• English premier league: Players, Clubs, Sponsors etc
• motor vehicle ownership: owners, vehicles, policies, claims etc

Question 15

common relationship types

Answer 15

• one-to-many: eg. customer places many orders
• many-to-many: requires linking entity to resolve
• one-to-one: a course has one lecturer

Question 16

what are domain entities

Answer 16

• core elements of a database (eg. employee, course)
• require careful linking to avoid may-to-many relationships

Question 17

what are linking entities

Answer 17

• used to resolve many-to-many relationships
• example: registration entity linking students to courses

Question 18

define lookup entities

Answer 18

complement domain entities and avoid NULL values

Question 19

define weak entities

Answer 19

depend on another entity for their existence
- example: media entity attributes for books, movies, magazines. Lookup entities prevent NULL values by separating unique attributes (eg. ISBN for books)

Question 20

example of ERD

Answer 20

• a customer places many orders (one-to-many)
• each order contains many products (many-to-many, resolved with orderLine entity)
• each customer has one representative (one-to-many)