WorksheetsTopic 1 - Topic 5: DB Design Concepts (Worksheet Extraction)
Total questions: 150
Worksheet time: 25mins
According to Barker’s Notation, which shape is used to represent an entity?
A hard-cornered rectangle
A soft box (rectangle with rounded corners)
A diamond
An oval
In database design, what is an "entity" primarily defined as?
Any noun found in a business document
Information of significance that the business needs to store and retrieve
A characteristic of a business object
The specific value of a data field
Which of the following is the standard naming convention for an Entity in the diagram?
Lowercase (e.g., employee)
CamelCase (e.g., EmployeeName)
Uppercase (e.g., EMPLOYEE)
Plural Nouns (e.g., EMPLOYEES)
What is an attribute?
A specific example of an entity
A relationship between two soft boxes
A characteristic that describes an entity
A unique number assigned to a table
How should attribute names be written in Barker’s Notation?
Uppercase (e.g., SURNAME)
Lowercase (e.g., surname)
Mixed case starting with uppercase (e.g., Surname)
It does not matter as long as it is consistent
If a business refers to an entity by more than one name, how should the synonym be represented in the diagram?
It should be written in brackets under the main entity name
It should be written in a separate soft box
It should be listed as an attribute
Synonyms are not allowed in Barker’s Notation
Which of the following best describes an "Instance"?
The blueprint of the database table
A specific, real-world occurrence of an entity (e.g., "Clifford Masoha" is an instance of STUDENT)
The connection between two entities
A mandatory attribute
What does UID stand for in the context of these slides?
User Interface Design
Unique Identifier
Unified Information Data
Universal Input Device
What is the primary purpose of a Unique Identifier (UID)?
To describe the physical characteristics of an entity
To distinguish one instance of an entity from another uniquely
To list all synonyms for an entity
To count how many attributes an entity has
If an entity is found to have no instances during the analysis phase, what does this usually imply?
It is a mandatory entity
It is a "warning light" that it might actually be an attribute, not an entity
It is the most important entity in the system
It is a duplicate entity
Which of the following would MOST likely be an attribute of an entity named VEHICLE?
Driver
registrationNumber
Road
TrafficLight
You are designing a system for a grocery store. Which of the following nouns is most likely an ENTITY?
Expiry Date
Product
Price
Weight
In a system for an international bank, why would "Exchange Rate" likely be an entity, whereas for a local bakery it would not?
Banks have more money than bakeries
The bakery only trades nationally, so the rate isn't "information of significance" to store
Exchange rates are numbers, and bakeries only store text
Exchange Rate is always an attribute, never an entity
Which text format correctly follows the standard for an attribute name consisting of two words?
dateofbirth
DateOfBirth
dateOfBirth
DATE_OF_BIRTH
If STUDENT is the Entity, which of the following represents an Instance?
studentNumber
21509345 (A specific student ID)
dateOfRegistration
Course
Why is a person's "Name" generally considered a poor Unique Identifier (UID)?
Names are too short
Names are hard to spell
Names are not unique; two people can have the same name
Names cannot be stored in a database
Which of the following pairs represents the correct relationship between Entity and Attribute?
Entity: EMPLOYEE | Attribute: empSalary
Entity: empName | Attribute: EMPLOYEE
Entity: Car | Attribute: Toyota
Entity: 10-05-2023 | Attribute: Date
If you choose to omit attribute names from the soft box in a diagram to save space, what must you do?
Delete the attributes from the system
Ensure they are documented in the supporting documentation
Rename the entity to include the attribute data
Use a larger font for the Entity name
Identify the error in this entity box representation: [ Box: STUDENT | Attributes: Name, Surname, ID ]
The entity name is singular
The attributes start with uppercase letters
The attributes are not numbered
The box has rounded corners
In the context of a school database, NWU and Eduvos are examples of:
Attributes of the Learner entity
Entities of the Department system
Instances of the COLLEGE entity
UIDs for the Student entity
Scenario: Mountain View Hospital. "Doctors rent consultation rooms within the building. If they need to perform surgical procedures on a patient, a theatre is booked." Based on this text, which of the following is NOT likely to be a primary Entity?
DOCTOR
THEATRE
PATIENT
RENT
Scenario: Employee Database. You need to find the salary of an employee named "Louise Richards". There are three employees with this name. What is the most efficient way to design the entity to solve this retrieval problem?
Create separate entities for each Louise Richards
Use the composite of Name + Surname as the UID
Create a synthetic, unique attribute like employeeNumber to use as the UID
Store the salary in the Name field
Analyze the following list of potential entities for a library system: BOOK, MEMBER, LOAN_DATE, LIBRARIAN. Which one should actually be modeled as an attribute?
BOOK
MEMBER
LOAN_DATE
LIBRARIAN
A business wants to store "Food Categories". One analyst suggests naming the entity FOODCATEGORY, another suggests Food Category. Which is the preferred notation according to the slides?
FOODCATEGORY (Single word, uppercase)
Food Category (Two words, mixed case)
foodCategory (CamelCase)
FOOD_CATEGORY (Snake_case)
You are reviewing a diagram. You see an entity CLASS with attributes: grade, division, educatorName. You also see an entity EDUCATOR with attributes name, email. What design improvement would you suggest?
Delete the EDUCATOR entity as it is redundant
Remove educatorName from CLASS and create a relationship to the EDUCATOR entity to avoid duplication and data inconsistency
Merge CLASS and EDUCATOR into one entity called SCHOOL
Make division the UID of CLASS
Scenario: Mountain View Hospital Billing. "The patient is billed for the use of the theatre, consumables and the use of the hospital bed." In modeling this, CONSUMABLE is identified as an entity. Which of the following would be a valid attribute for CONSUMABLE?
patientName (The person using it)
costPrice (The cost of the item)
theatreNumber (Where it is used)
doctorID (Who used it)
Why might a business decide NOT to include "Employee Age" as a stored attribute in the EMPLOYEE entity, even though it is significant information?
Age is a number and databases don't handle numbers well
Age is derived data; it changes every year and can be calculated from dateOfBirth
Storing age is a security violation
Age is an entity, not an attribute
You are defining a UID for the entity CAR. You have the following attributes: colour, model, ownerName, vehicleIdentificationNumber (VIN). Which is the best choice for UID?
model (Because models are standard)
ownerName (Because owners are unique people)
vehicleIdentificationNumber (Because it is unique to the specific car instance)
colour + model (Because the combination is rare)
If an entity PROSPECT is created for a sales system, but after 6 months of data entry, every single PROSPECT becomes a CLIENT immediately and no PROSPECT data exists, what should be concluded about PROSPECT?
It should remain as a mandatory entity
It should be converted into an attribute (e.g., a clientStatus) and removed as a separate entity
A new UID should be created for PROSPECT to track future data
CLIENT should be split into multiple entities
Independently, what should you likely do?
Delete the CLIENT entity.
Re-evaluate if PROSPECT and CLIENT are actually the same entity and merge them.
Keep them separate to increase the number of entities in the diagram.
Make PROSPECT an attribute of SALES.
In the Mountain View Hospital scenario, "Northern Gauteng" is mentioned as the location. Why would LOCATION likely NOT be an entity in this specific system?
It is a proper noun.
The scenario implies the hospital is a single facility in one location, so location data is constant/irrelevant to patient invoicing structure.
Locations cannot be entities.
"Gauteng" is too long to be an entity name.
In an Entity Relationship Diagram (ERD), what does a "relationship" represent?
A permanent storage container for data
A real-world association between entities
The name of the database administrator
A list of attributes
Relationships are described as "bidirectional." What does this mean?
They only flow from Parent to Child.
They allow data to flow into the database but not out.
They flow in two directions (e.g., Employee works for Department; Department consists of Employees).
They connect two databases together.
In Barker’s Notation, which line style represents a MANDATORY relationship?
A dotted line
A solid line
A double line
A curved line
Which line style represents an OPTIONAL relationship (one that "may" exist)?
A dotted (dashed) line
A solid line
A bold line
A red line
What symbol represents a degree of "one or more" (Many)?
A single vertical line
A circle
A crow’s foot
A diamond
Which component of a relationship tells us if an instance "must" or "may" exist?
Degree
Optionality
Uniqueness
Syntax
How should relationship names be written on the diagram?
Uppercase, inside the entity box
Lowercase, close to the entity to which it refers
CamelCase, on the relationship line
It does not matter
What is the most common type of relationship found in database design?
One-to-one
Many-to-many
Many-to-one
None-to-none
In a "Parent-Child" relationship, which entity is the "Child"?
The entity on the "one" side
The entity with the crow’s foot against it
The entity that exists first
The optional entity
What is an "Intersection Entity" used for?
To create one-to-one relationships
To resolve many-to-many relationships
To delete data
To name attributes
Read the following statement: "Each EMPLOYEE must belong to one and only one DEPARTMENT." Which diagram notation matches this?
A dotted line with a crow’s foot near Department.
A solid line with a single line near Department.
A solid line with a crow’s foot near Department.
A dotted line with a single line near Employee.
If you see a dotted line connecting Entity A and Entity B, what does it imply about the creation of data?
Both entities must be created at the exact same time.
Entity A is dependent on Entity B.
The entities can be created separately, and the link added later.
The relationship is invalid.
Why are "One-to-One" relationships considered rare?
Databases cannot handle them.
They usually imply that the two entities are actually the same thing and should be merged.
They require too much storage space.
They are only used for financial data.
If a relationship is Mandatory (solid line) on BOTH sides, what does this imply?
The entities are completely independent.
Both entities must be created simultaneously, or checks must be disabled during creation.
It is a many-to-many relationship.
One entity is the parent and the other is the child.
Identify the Parent entity in this scenario: "Each CUSTOMER may place one or more ORDERS."
ORDER
CUSTOMER
Both are parents
Neither (This is a many-to-many relationship)
When verifying a relationship by reading it aloud, which pattern should you follow?
[Entity1] [Relationship Name] [Entity2]
Each [Entity1] [Optionality] [Relationship Name] [Degree] [Entity2]
[Degree] [Entity1] [Name] [Entity2]
[Entity1] connects to [Entity2]
You are analyzing a diagram. Entity A has a crow's foot connecting to Entity B. Entity B has a single line connecting to Entity A. What type of relationship is this?
One-to-One
Many-to-Many
Many-to-One
Recursive
What does "Referential Integrity" prevent in a Parent-Child relationship?
Creating a child entity.
Deleting a parent instance if it still has associated child instances.
Renaming the relationship.
Having optional relationships.
In the diagram LOAN -----------< CUSTOMER (with a > 4 on the loan side), what does this specific notation mean?
A customer must have exactly 4 loans.
A customer can have a maximum of 3 loans.
A customer must have more than 4 loans.
A customer is limited to a quantified degree (e.g. max 3 loans).
If you find a "Many-to-Many" relationship in your final diagram, what step have you likely missed?
Naming the relationship.
Resolving it by creating an intersection entity.
Making it mandatory.
Drawing the soft boxes correctly.
Scenario: University Database. "A Student takes many Modules. A Module is taken by many Students." How should this be modeled in the final design?
Draw a solid line with crow's feet on both ends between STUDENT and MODULE.
Create a new entity (e.g., STUDENT_MODULE) as a child of both STUDENT and MODULE.
Merge STUDENT and MODULE into one entity.
Make the relationship optional on both sides.
Scenario: Nice Coffee. "Jill works as a shop manager in France while Werner is a financial administrator in Germany. All employees work for various departments." Based on the text, what is the relationship between EMPLOYEE and DEPARTMENT?
One-to-One (One employee manages one department).
Many-to-Many (Employees work in many departments).
Many-to-One (Each Department consists of one or more Employees; Each Employee works for one Department).
Optional (Employees don't need departments).
Scenario: Movie Theatre. You are designing a system where a MOVIE entity describes the film, and a THEATRE entity describes the physical building. A movie can be shown in many theatres, and a theatre shows many movies. What is the "Intersection Entity" most likely to be named?
TICKET
SHOW (or SCHEDULE)
SEAT
PROJECTION
You have an entity PERSON and an entity EMPLOYEE. The rule is: "Each EMPLOYEE must be a PERSON. Each PERSON may be an EMPLOYEE." If you decide to keep them as separate entities (One-to-One), which relationship line is correct?
Solid line from Person to Employee
Dotted line from Person to Employee (Person may be Employee).
Crow's foot on Employee side.
Crow's foot on Person side.
In a LOAN system, a rule states: "Each LOAN must belong to one and only one CUSTOMER." If a user tries to delete a CUSTOMER who currently has active LOANs in the database, what should happen?
The system should delete the customer and leave the loans as "orphans."
The system should automatically delete the loans too.
The system should prevent the deletion until the loans are removed (Referential Integrity).
The system should rename the customer to "Unknown."
Which of the following is the correct "reading" of the relationship for the Nice Coffee scenario regarding Head Offices? "All employees are on the payroll of one of the National Head Offices."
Each HEAD OFFICE may employ one or more EMPLOYEES.
Each EMPLOYEE must be paid by one and only one HEAD OFFICE.
Each EMPLOYEE may be paid by many HEAD OFFICES.
Each HEAD OFFICE must employ exactly one EMPLOYEE.
You are reviewing a diagram and see > 1 written next to a crow's foot on the EMPLOYEE side of a DEPARTMENT relationship. What business rule does this represent?
A department can exist with zero employees.
A department must have more than one employee to be valid.
An employee must work for more than one department.
A department has exactly one employee.
If you have an entity ORDER and an entity PRODUCT, and the rule is "An order can contain multiple products, and a product can appear on multiple orders," where do you store the attribute Quantity (how many of this product are on this order)?
In the ORDER entity.
In the PRODUCT entity.
In the intersection entity (e.g., ORDER_LINE or SELECTION).
It cannot be stored.
Why must the Parent entity exist before the Child entity in a mandatory relationship?
Because the Child is dependent on the Parent.
Because the Parent is larger.
Because the Parent has more attributes.
It doesn't matter; they can be created in any order.
In the Nice Coffee scenario, if Sales, Finance, and Admin are mentioned, these are examples of:
Attributes of the Employee.
Instances of the DEPARTMENT entity.
Separate Entities.
Relationships.
According to the guidelines for ER Diagram layout, how should lines be drawn?
As curved arcs connecting entities.
Using only horizontal, vertical, or diagonal lines.
As jagged, lightning-bolt shapes to indicate energy.
In any direction that fits the page.
What technique should be used if a relationship line must cross another line in the diagram?
Use a different color for one of the lines.
Use a "bump" or "bridge" symbol to show they are not related.
Erase one of the lines.
Place a circle at the intersection point.
Which symbol is used to tag a MANDATORY attribute in the diagram?
An asterisk (*)
A hash (#)
A lowercase 'o'
A plus sign (+)
Which symbol is used to tag an OPTIONAL attribute in the diagram?
An asterisk (*)
A question mark (?)
A lowercase 'o'
An exclamation mark (!)
What is "Metadata"?
Data about other data, such as structure and location.
The total sum of all sales in a year.
Data that is duplicated across multiple tables.
The physical hard drive where data is stored.
Which of the following is an example of a "Domain"?
A specific employee's name.
A list of specific values like days of the week or Yes/No choices.
The connection between two entities.
A mandatory relationship.
"Fact Data" is best described as:
Data about the structure of the database.
The bulk of all data that forms the core of the system.
Pre-computed summaries used for performance.
Data that never changes.
What is "Derived Data"?
Data that is imported from another system.
Data that can be calculated from other stored values (e.g., totals or averages).
Data that is used as a primary key.
Data that is strictly confidential.
How should attribute names be formatted in the diagram?
ALL CAPS (e.g., STARTDATE).
camelCase (e.g., startDate).
Snake_case (e.g., start_date).
PascalCase (e.g., StartDate).
What is "Reference Data"?
Data that changes every second.
Data that is assumed not to change frequently (e.g., lookup lists).
Data calculated on the fly.
Metadata about user access rights.
Why should you avoid storing "Derived Data" (like a total price) in the database?
It is illegal to store math results.
It takes up unnecessary space and can lead to inconsistent values.
It makes the diagram look cluttered.
It prevents the use of foreign keys.
When is "Duplication" of data (Common Data) considered acceptable?
When the designer forgets to normalize.
When it benefits the user by making data more easily accessible in a massive system.
When the data is numeric.
Never; duplication is always a critical error.
If an attribute called "Date" is found in an entity, why is this considered poor naming?
It is too short.
It is ambiguous; it could mean date ordered, date filled, or date born.
"Date" is a reserved word in all languages.
Attributes cannot start with the letter D.
If you discover that an attribute needs to store multiple values (e.g., a Member taking out multiple Books), what does this indicate?
You should use a larger data type.
The attribute violates the rule that it can only hold one value, and a new entity (or intersection entity) is needed.
You should duplicate the attribute (Book1, Book2, Book3).
You should make the attribute optional.
In the context of "Allowable Values," what does a range like [1–9] represent?
The number of bytes the data takes up.
A limitation on what specific values can be entered into that attribute.
The default value.
The number of users who can access the data.
Why might a designer choose to store derived data despite the redundancy?
To improve the system's overall performance if processing overhead is an issue.
To make the diagram look bigger.
Because storage space is expensive.
Because the client requested it.
If an attribute is marked as "Mandatory" (*), what does this mean for data entry?
It can be left blank until the record is updated later.
It must have a value stored as soon as a new instance is created.
It allows NULL values.
It is a primary key.
What is the purpose of "Summary Data"?
To trace the origins of values.
To improve query performance by using pre-computed facts from one or more sources.
To store the history of database changes.
To list all the tables in the database.
How does using "Domains" benefit the database designer?
It automatically draws the diagram.
If changes need to be made (e.g., to a currency format), they only need to be made in one place.
It prevents the use of SQL.
It removes the need for optional attributes.
If an entity has too many attributes to fit in the soft box, what is the preferred solution?
Do not include the attributes in the system.
Draw the soft box larger.
Write them on the back of the page.
Split the entity into two random entities.
Scenario: Employee Database. You are designing an EMPLOYEE entity. You have the attribute dateOfBirth. You also want to know the employee's current Age. How should you handle Age?
Create a mandatory attribute * age.
Create an optional attribute o age.
Do not store Age as an attribute; calculate it from dateOfBirth to avoid redundancy.
Create a new entity called AGE.
Scenario: Product Pricing. A business needs to store the Price of a product. However, they also need to keep a history of price changes over time to see what the price was last year. What is the correct modeling solution?
Add attributes oldPrice1, oldPrice2, oldPrice3 to the Product entity.
Price should become a separate entity (e.g., PRICEHISTORY) linked to the Product.
Keep overwriting the Price attribute with the new value.
Create a Many-to-Many relationship between Product and Price.
Scenario: Library Loan System. A MEMBER has a name and ID. A BOOK has a title and ID. If a member takes out 4 books, and you try to store "Book Title" inside the MEMBER entity, what rule is violated?
Optionality rule.
An attribute can only hold one value at a specific time.
Domain integrity rule.
Naming convention rule.
Scenario: Accounts Department. You find an entity ACCOUNT with a single attribute called date. Upon interview, you find this refers to both "Invoice Date" and "Payment Date". How do you fix this?
Rename the attribute to invoiceAndPaymentDate.
Split the attribute into two separate attributes: dateSent and dateOfPayment.
Create a separate entity for Dates.
Make the attribute optional.
You are documenting an attribute empSalary. Which of the following details would be part of the "Attribute Documentation"?
Data Type: Money/Currency.
The name of the person who hired the employee.
The font size used in the diagram.
The relationship to the Department entity.
Scenario: Vehicle Registration. A system records VehicleColor. The business uses a standard set of colors (Red, Blue, White, Black) and does not allow custom colors. This list of colors is an example of:
Metadata.
A Domain (Specific list of values).
Derived Data.
Summary Data.
You are designing a diagram and see two relationship lines crossing. To ensure "Tidiness" and avoid confusion, you should:
Use a "bump" where the lines cross.
Stop the line and restart it after the crossing.
Label the intersection "Crossing".
There is no rule for this.
An attribute cellNumber is marked with a lower case o. In the context of the business, what does this imply?
Every employee has a cell phone.
It is a default value.
The attribute allows NULL values (an employee might not have a cell number).
The cell number is a primary key.
A Database Administrator (DBA) needs to trace who owns a specific set of data and what the access rights are. What category of data would they consult?
Fact Data.
Metadata.
Reference Data.
Summary Data.
Scenario: Sales System. You have an entity ORDER with attributes orderDate and customerName. You decide to add default value = Today's Date to the orderDate attribute documentation. This practice is most helpful for:
Optional attributes.
Mandatory attributes.
Derived attributes.
Identifying Primary Keys.
What symbol is used to identify a Unique Identifier (UID) in Barker’s Notation?
An asterisk (*)
A hash (#)
A dollar sign ($)
A double underline
What is a "Simple UID"?
A UID that is made up of two or more attributes.
A UID consisting of a single attribute that uniquely identifies an instance.
A UID that is optional.
A UID used only for small tables.
Which of the following is an example of a "Natural Key"?
A) A system-generated random number.
B) An attribute found within the business data, like an employee number or product code.
C) A key created by the RDBMS that has no business meaning.
D) A foreign key.
What is a "Surrogate Key" (or Synthetic Key)?
A) A key based on existing business data.
B) A duplicate key used for backup.
C) An RDBMS-generated UID that is not based on existing data.
D) A key that allows null values.
What is a "Compound UID"?
A) A UID that changes over time.
B) A combination of attributes used to identify an instance uniquely.
C) A UID that is shared between two entities.
D) A UID that contains text and numbers.
Why might a business use a Surrogate Key instead of a Natural Key?
A) Because Natural Keys are too expensive.
B) When a suitable attribute cannot be found within the business data.
C) Because Surrogate Keys are easier to spell.
D) Because Natural Keys cannot be indexed.
In a diagram, where is the # symbol placed for a Compound UID?
A) Only next to the first attribute.
B) At the bottom of the soft box.
C) In front of each contributing attribute.
D) Next to the entity name.
When combining an attribute and a relationship to form a UID, what is this often called?
A) A Simple UID.
B) A Composite UID.
C) A Recursive UID.
D) A Hidden UID.
How is a UID relationship indicated on the relationship line itself?
A) By a small bar drawn across the relationship line.
B) By a double arrow.
C) By a dotted line.
D) By coloring the line red.
"Multiple UIDs" (Candidate Keys) are numbered in the diagram (e.g., #1, #2). What does this numbering indicate?
A) That they are part of one large composite UID.
B) That they are alternative ways to uniquely identify the entity.
C) The order in which they must be entered.
D) The security level of the data.
Why is using a person's "Name" and "Surname" alone often considered a poor Compound UID?
A) It takes up too much storage space.
B) It is impractical because it might not be unique (two people can have the same name).
C) You cannot put a # symbol next to text.
D) Names are optional attributes.
In the BOOKING entity example, the UID is composed of guestID, roomNo, startDate, and endDate. What type of UID is this?
A) Simple UID.
B) Compound UID.
C) Surrogate UID.
D) Recursive UID.
If you see a relationship line with a "Bar" across it near the LOAN entity, pointing to MEMBER, what does this mean?
A) The relationship is forbidden.
B) The relationship is optional.
C) The relationship to MEMBER is part of the UID for LOAN.
D) The MEMBER entity is the child of LOAN.
What happens if you try to create a second instance of a LOAN for the exact same MEMBER and BOOK without adding a date to the UID?
A) The system creates a duplicate.
B) It is not allowed because the instance would not be unique (you can't distinguish the two loans).
C) The old loan is deleted.
D) The book title changes.
In a Multi-level UID scenario (like the Movie Ticket example), why does SHOW need the UID from THEATRE?
A) Because theatres are bigger than shows.
B) Because a Movie can be shown in more than one Theatre at the same time, so the Theatre number is needed to be unique.
C) It doesn't; this is an error in the diagram.
D) Because the SHOW entity has no attributes of its own.
If an entity has #(1) * vin and #(2) * plate, what does this tell you?
A) You need both VIN and Plate to identify the car.
B) VIN is the primary key, and Plate is a foreign key.
C) The entity has two different, separate ways to be uniquely identified.
D) This is a composite UID.
Why is a UID rarely comprised of optional attributes?
A) Optional attributes are text-only.
B) Because a UID must identify an instance, and if the value is missing (null), it cannot identify anything.
C) Optional attributes are too long.
D) It is allowed and very common.
In the TICKET entity example, the UID includes seatNumber and relationships to parents. What does TICKET "inherit"?
A) The color of the seat.
B) The UIDs of the parent entities (SHOW, which includes MOVIE and THEATRE).
C) The price of the movie.
D) Nothing, it is independent.
When resolving a Many-to-Many relationship, what usually happens to the UIDs?
A) The new intersection entity often uses a Composite UID made of the UIDs from the two original parents.
B) The UIDs are deleted.
C) A new simple ID is always created automatically.
D) The relationship becomes optional.
Which of the following best describes the "Bar" notation on a relationship?
A) It is drawn on the parent side.
B) It is drawn on the side of the entity where the UID is applicable (the child/intersection entity).
C) It indicates a "Many" relationship.
D) It indicates a "One-to-One" relationship.
Scenario: Library Loan. You have MEMBER (memID) and BOOK (bookID). If the UID for LOAN is just memID + bookID, what business rule does this enforce?
A) A member can borrow many books.
B) A member can borrow the same book many times.
C) A member can borrow a specific book only once (because the combination must be unique).
D) A book can be borrowed by many members at the same time.
Scenario: Library Loan (Improved). To allow a member to borrow the same book again on a different day, what must be added to the UID of LOAN?
A) loanAmount.
B) loanDate (or a timestamp).
C) librarianName.
D) condition.
Scenario: Movie Ticket. A TICKET is identified by seatNumber. However, seatNumber "A1" exists for every show. To make the TICKET unique, what relationship must be part of the UID?
A) The relationship to the POPCORN entity.
B) The relationship to the SHOW entity.
C) The relationship to the ACTOR entity.
D) The relationship to the WEBSITE entity.
Scenario: Employee Identification. An EMPLOYEE entity has: #(1) * empID #(2) * taxNum #(3) * surname #(3) o name What is wrong with UID #3?
A) surname should be optional.
B) name is an optional attribute, which is generally not allowed in a UID.
C) taxNum is too short.
D) You cannot have three UIDs.
Scenario: Stock Control. You have STORE (storeID) and ITEM (itemID). You want to track the stock level of items in specific stores. You create an intersection entity STOCK. What is the most logical UID for STOCK?
A) A new attribute stockID.
B) The storeID alone.
C) A composite UID of storeID (relationship) + itemID (relationship).
D) The quantity attribute.
Scenario: Vehicle Registration. A Traffic Register lists the VIN of all vehicles. It also lists the LicensePlate assigned to the vehicle. How would you model this?
A) Create two entities: VIN and PLATE.
B) Use a Compound UID: VIN + PLATE.
C) Use Multiple UIDs: #(1) VIN and #(2) Plate.
D) Make VIN the UID and ignore Plate.
You are designing a SHOW entity for a cinema. A show is defined as a specific MOVIE playing at a specific THEATRE at a specific TIME. Which attributes/relationships form the UID?
A) Movie + Theatre.
B) Movie + Theatre + Time.
C) Time only.
D) Movie + Time.
If COURSE (courseID) and STUDENT (studentID) have a Many-to-Many relationship, and you resolve it with ENROLLMENT, but you DO NOT make the relationships part of the UID, what must you do?
A) Use studentID as the Primary Key.
B) Create a Surrogate Key (e.g., enrollmentID) for the ENROLLMENT entity.
C) Leave the entity without a UID.
D) Use courseID as the Primary Key.
In a "Multi-level UID" hierarchy (Grandparent -> Parent -> Child), how does the Child's UID behave?
A) It ignores the Grandparent.
B) It creates its own random number.
C) It effectively "inherits" the UIDs of the Parent (which includes the Grandparent's UID) plus its own unique attribute.
D) It becomes a Many-to-Many relationship.
Scenario: Hotel Booking. BOOKING entity has guestID and roomNo as part of its UID. If roomNo was not part of the UID, what would happen?
A) A guest could book multiple rooms for the same night.
B) A guest could only book one room per night (because Guest + ...).
C) The hotel could not assign rooms anymore.
D) The booking would automatically include payment details.
What is the primary problem with Many-to-Many (M:N) relationships in a physical database?
They take up too much color in the diagram.
They cannot be directly implemented and cause issues with data retrieval and attribute placement.
They are only allowed for financial data.
They create too many primary keys.
What type of entity is used to resolve a Many-to-Many relationship?
A Parent Entity.
An Intersection Entity (also called a Linking or Associative Entity).
A Surrogate Entity.
A Duplicate Entity.
When resolving an M:N relationship between Entity A and Entity B, where is the new Intersection Entity placed?
Inside Entity A.
In between the two involved entities (Entity A and Entity B).
On a separate page.
It replaces both entities.
What relationship degree does the Intersection Entity have with its parents?
Many-to-Many.
One-to-One.
Many-to-One (The intersection is the "Many" side).
Optional.
In terms of hierarchy, the Intersection Entity is considered the ______ of the two original entities.
Parent.
Child.
Sibling.
Owner.
Which optionality is required for the relationships leading from the Intersection Entity to the parents?
Mandatory (Solid line).
Optional (Dotted line).
It depends on the business rule.
It must be one mandatory and one optional.
How is the UID of an Intersection Entity typically formed?
By creating a random number.
By using the "Date" attribute only.
It is composed of the relationships to its two parent entities (Composite UID).
It does not have a UID.
If the relationships to the parents are not enough to make the Intersection Entity unique, what should be done?
Delete the entity.
Add an attribute (like date or time) to the UID.
Make the relationships optional.
Combine the two parent entities into one.
When naming the relationship from the Intersection Entity to a parent, what simple word is often suggested if no specific business term exists?
"Has".
"Is".
"For".
"And".
What symbol represents the Intersection Entity's side of the relationship?
A single line.
A Crow's Foot.
A Circle.
A Double Line.
Why does the "Quantity" attribute often fail in a direct M:N relationship between ORDER and PRODUCT?
Because numbers cannot be attributes.
If placed in PRODUCT, it implies the product always sells in that quantity. If placed in ORDER, you don't know which product that quantity refers to.
Because Quantity is a reserved word.
Because Order and Product cannot be related.
In the STUDENT and MODULE example, what happens if you resolve the relationship but do NOT include Date in the UID of the intersection entity?
The student fails the module.
The student cannot take the same module more than once (e.g., if they fail and need to repeat it).
The module ceases to exist.
The database crashes.
In a resolved diagram for PIZZA and FILLING (Toppings), the intersection entity PIZZA_FILLING would likely contain which specific attribute?
pizzaSize.
fillingName.
amount (e.g., double cheese, extra pepperoni).
deliveryTime.
Why is the Intersection Entity always on the "Many" side (Crow's Foot)?
Because one Parent instance (e.g., one Student) can be associated with many Intersection instances (e.g., many Enrollments).
Because it contains more data.
Because it is optional.
Because it is physically larger on the diagram.
If you have DOCTOR and PATIENT with a M:N relationship, and you create APPOINTMENT in the middle, why must APPOINTMENT be mandatory to DOCTOR?
Because doctors are important.
Because an appointment cannot exist without a doctor to conduct it (referential integrity).
Because doctors have many patients.
It should be optional.
When creating a Composite UID for an Intersection Entity using Barker's Notation, how do you mark the relationships?
Put a # next to the relationship line or bar.
Put a bar across the relationship line near the Intersection Entity.
Color the line blue.
You don't; you only mark attributes.
Scenario: STORE and PRODUCT. You want to track StockLevel. Why is StockLevel an attribute of the Intersection Entity?
Because StockLevel is a number.
Because a Product has different stock levels in different Stores (it depends on both).
Because the Store entity is full.
It should be in the Product entity.
If you have an entity SALE between CAR and DEALERSHIP, what does the attribute numOfSales likely represent if it is in the SALE entity?
The total sales of the dealership.
The number of cars sold in that specific transaction context (though usually 1 for cars).
An error; numOfSales implies summary data which shouldn't be stored.
The ID of the car.
Which of the following is a valid name for an Intersection Entity between INVOICE and PRODUCT?
INVOICE_PRODUCT.
INVOICE LINE (or LINE ITEM).
SELECTION.
All of the above are valid options.
If you resolve a M:N relationship but leave the relationships as "Optional" on the Intersection Entity side, what logical error have you made?
You have created an "Orphan" record that links to nothing.
You have made it a 1:1 relationship.
You have created a recursive relationship.
There is no error.
Scenario: Pizza Shop. You have PIZZA and FILLING. A pizza can have many fillings, and a filling can be on many pizzas. You create PIZZA_FILLING. What is the primary UID for this new entity?
A new PF_ID.
Composite: Pizza (relationship) + Filling (relationship).
fillingID only.
amount.
Scenario: Sailboat Rental. PERSON rents SAILBOAT. A person can rent many boats over time. A boat is rented by many people over time. To allow the same Person to rent the same Boat multiple times, what must be added to the UID of the RENTAL intersection entity?
rentalDate or time.
boatColor.
personName.
Nothing is needed.
Scenario: Medical System. Entities: DOCTOR (#docID), PATIENT (#patID). Intersection: APPOINTMENT. If the UID of APPOINTMENT is docID + patID + date, what does this restriction mean for the patient?
The patient can see many doctors on the same day.
The patient can see the same doctor only once per day.
The patient can never see the same doctor again.
The patient cannot make an appointment.
Scenario: Soft Drink Orders. ORDER and DRINK. Intersection Entity: SELECTION. Where does the attribute Quantity (e.g., 2 Cokes) belong?
In ORDER (because the customer pays for it).
In DRINK (because it describes the product).
In SELECTION (because it describes how many of that drink are in that order).
It is derived data and shouldn't be stored.
Scenario: Student Grades. STUDENT and MODULE. Intersection: ENROLLMENT. Attribute: Mark (Final Grade). Why is Mark placed in ENROLLMENT?
Because STUDENT contains personal info, not grades.
Because MODULE contains course info, not grades.
Because the mark belongs to a specific instance of a Student taking a specific Module.
All of the above.
You are reviewing a diagram. You see: AUTHOR >--< BOOK. (M:N). What is the correct first step to resolve this?
Delete AUTHOR.
Draw a new box in the middle called AUTHORSHIP (or similar).
Change the line to a solid line.
Add an attribute bookTitle to AUTHOR.
Scenario: Sales Commission. SALESPERSON and PRODUCT. A salesperson sells many products. A product is sold by many salespeople. You need to track the CommissionPaid for each specific sale. Where does CommissionPaid go?
SALESPERSON entity.
PRODUCT entity.
The Intersection Entity (e.g., SALE).
It is metadata.
If an Intersection Entity has a UID composed of Relationship A + Relationship B + Attribute C, what type of UID is this?
Simple UID.
Composite UID.
Surrogate UID.
Unnatural UID.
In the Group 3 diagram (Person/Sailboat), the intersection entity is labeled RENT or HIRE. Why is ownerID NOT in this entity?
Because the owner owns the boat, not the rental transaction.
Because ownerID is text.
Because the owner is renting the boat.
It should be there.
