Lecture 1 Activities

Activities from Lecture 1

Activity 1

Sheridan College HR maintains a database of Employees. State whether each of the following represents data or metadata. If it represents data, state whether it is structured or unstructured data. If it represents metadata, state whether it is fact describing a property of data or fact describing the context of the data. Include a brief justification for each of your answers.

a. Employee’s name, address and birth date

b. The fact that the employee’s name is a 30-character field

c. A photo image of the employee

d. The employee’s ID number  

e. The employee’s phone number and emergency contact (phone number)

f. The fact that the employee’s birth date must precede today’s date by at least 16 years.

Solution

a) Structured Data

  • Because it…

    • Is factual info

    • Stored in predefined fields

    • Follows a structured format

b) Metadata — description (describing a property of data)

  • Bc it describes a characteristic of a data field (i.e. ‘name’ field)

c) Unstructured Data

  • Bc images do not follow structure format or predefined schema, therefore are unstructured data

d) Structured Data

  • Bc ID numbers are factual info

  • Stored in predefined field

e) Structured Data

  • Bc it is factual info

  • Stored in predefined field

f) Metadata — context (describing the context of the data)

  • Because it defines a rule regarding data

  • Specifies data’s (i.e. metadata’s) context

Activity 2

For each of the following pairs of related entities, indicate whether there is a one-to-many or a many-to-many relationship. Then using shorthand notation introduced in the text, draw a diagram for each of the relationships.

  1. Order and Customer 

  2. Transaction and Account

  3. Store and Employee 

  4. INSTRUCTOR and COURSE (instructors teach course)

Activity 2

For each of the following pairs of related entities, indicate whether there is a one-to-many or a many-to-many relationship. Then using shorthand notation introduced in the text, draw a diagram for each of the relationships. Justify your answer.

  1. Order and Customer 

  2. Transaction and Account

  3. Store and Employee 

  4. INSTRUCTOR and COURSE (instructors teach course)

*IMP NOTE: When asked to create an entity-relationship diagram on assessments, you MUST ALWAYS explain/justify your answer. If you don’t, your answer will be marked wrong! This is crucial because many times there are multiple correct answers depending on your perception of the relationship between two entities.

Solutions

(1) Order & Customer

Relationship Type: One-to-Many (1:M)

Entity-Relationship Diagram (ERD):

(2) Transaction & Account

Relationship Type: One-to-Many (1:M)

Entity-Relationship Diagram (ERD):

(3) Store & Employee

Relationship Type: One-to-Many (1:M)

Entity-Relationship Diagram (ERD):

(4) Instructor & Course

Relationship Type: Many-to-Many (1:M)

Entity-Relationship Diagram:

AI-Generated Activities (Extra Practice Qs)

Activity 3

Determine whether each the following represent data or metadata. If its data, specify if it is unstructured or structured. If its metadata, specify if it describes a property of data or provides context regarding the data. Briefly justify your answers.

  1. The department to which an employee belongs.

  2. The fact that the employee’s ID must be unique in the database.

  3. A video introduction recorded by the employee.

  4. The date and time when the employee's record was last updated.

  5. The employee's salary and benefits details.

  6. The fact that the employee’s salary must be a positive number.

  7. The employee's resume uploaded as a PDF document.

  8. A flag indicating whether the employee is full-time or part-time.

  9. The database field name Employee_Email.

  10. The fact that the email address must follow a valid email format (e.g., name@example.com).

Solutions

  1. Structured Data

    • Justification: The department is stored in a predefined field with a set format, such as a department ID or name, making it structured data.

  2. Metadata Describing the Context of the Data

    • Justification: The rule that the ID must be unique defines a constraint, describing the context of the data stored in the Employee_ID field.

  3. Unstructured Data

    • Justification: Videos do not follow a structured tabular format and are stored as binary objects, making them unstructured data.

  4. Structured Data

    • Justification: Timestamps are typically stored in a predefined field with a defined format (e.g., YYYY-MM-DD HH:MM:SS), making them structured data.

  5. Structured Data

    • Justification: Salary and benefits are numeric or text fields stored in a well-defined format in the database, making them structured data.

  6. Metadata Describing the Context of the Data

    • Justification: The rule that salary must be positive defines a validation requirement for the data, describing its context.

  7. Unstructured Data

    • Justification: Resumes as PDF documents do not conform to a tabular or predefined format, making them unstructured data.

  8. Structured Data

    • Justification: Flags (e.g., True/False or Full-Time/Part-Time) are stored in defined fields with clear structures, making them structured data.

  9. Metadata Describing a Property of Data

    • Justification: The field name Employee_Email is part of the database schema, describing a property of the data (its name or label).

  10. Metadata Describing the Context of the Data

    • Justification: The rule about email format is a validation rule that defines the context in which the data is valid.

Activity 4

For each of the following pairs of related entities, indicate whether there is a one-to-many (1:M) or a many-to-many (M:N) relationship. Then, using shorthand notation, draw a diagram for each relationship.

  1. Book and Author (An author writes books)

  2. Patient and Doctor (A patient visits doctors)

  3. Department and Employee (An employee belongs to a department)

  4. Student and Course (A student enrolls in courses)

Solutions