Learning Outcome 001

Question 1

Determine end-user views, outputs, and transaction processing requirements.

Answer 1

Understand what data users need to view, what outputs (reports, dashboards) they require, and how transactions (e.g., insert, update, delete) will be processed.

Question 2

Determine entity types.

Answer 2

Identify the main objects or concepts (e.g., Customer, Product, Order) that will be represented in the database.

Question 3

Identify relationship types.

Answer 3

Define how entities are related (e.g., a Customer places an Order, an Order contains Products).

Question 4

Identify attributes.

Answer 4

List the properties or details of each entity (e.g., Customer attributes: Name, Address, Phone).

Question 5

Determine attribute domains.

Answer 5

A ‘Domain Attribute’ in Computer Science refers to an attribute used to reference another entity, particularly a member within that entity. It helps establish relationships between different entities in a database system.

Question 6

Determine candidate key.

Answer 6

A minimal set of attributes that can uniquely identify each record in a table, ensuring data integrity and allowing for efficient data retrieval.

•Example: In a Customer table, both CustomerID and Email could be candidate keys.

Question 7

Determine Primary Key

Answer 7

The primary key is the candidate key chosen to uniquely identify each record in a table.

•Example: In a Customer table, CustomerID is selected as the primary key.

Question 8

Determine Alternate Key

Answer 8

Alternate keys are candidate keys that are not selected as the primary key but still uniquely identify records.

•Example: In a Customer table, Email could be an alternate key if CustomerID is the primary key.

Question 9

Steps to Determine Keys

Answer 9

1. Identify Candidate Keys:
   
   • Look for attributes or combinations of attributes that can uniquely identify records.
   • Example: In a Student table, StudentID and SocialSecurityNumber might both be candidate keys.
2. Select the Primary Key:
   
   • Choose the most appropriate candidate key based on:
     
     • Stability: The key should not change (e.g., StudentID is better than Email).
     • Simplicity: Prefer single-attribute keys over composite keys.
     • Usage: Consider how the key will be used in queries and relationships.
   • Example: Select StudentID as the primary key.
3. Designate Alternate Keys:
   
   • Identify other candidate keys that can serve as unique identifiers if needed.
   • Example: Designate SocialSecurityNumber as an alternate key.
4. Implement Constraints:
   
   • Apply unique constraints to alternate keys to ensure data integrity.
   • Example: Ensure no two students have the same SocialSecurityNumber.

Question 10

Check model for redundancy.

Answer 10

Ensure no unnecessary duplication of data exists in the model.

Question 11

Validate the Conceptual Model Against User Transactions

Answer 11

Validation: Ensure that the model supports all required user transactions and outputs.

Question 12

Review the Conceptual Data Model with User

Answer 12

Review: Present the model to users for feedback and make necessary adjustments.

Question 13

1.2 Identify an Appropriate DBMS

Evaluate Database Models

Answer 13

Database Models: Consider relational, NoSQL, object-oriented, etc., based on the application requirements.

Question 14

Choose a DBMS Based on

Answer 14

Choose a DBMS Based on:

-Pricing: Consider the cost of the DBMS, including licensing, maintenance, and support.

-Existing Database Migration: Evaluate the ease of migrating existing data to the new DBMS.

-Functionality and Business Needs: Ensure the DBMS meets the functional requirements of the business.

-Scalability: Consider the ability of the DBMS to handle growth in data volume and user load.

Question 15

1.3 Normalize Database Relations

Define Database Normalization

Answer 15

Normalization: The process of organizing data to reduce redundancy and improve data integrity.

Question 16

Outline the objectives of database normalization.

Answer 16

Eliminate duplicate data, ensure data dependencies make sense, and simplify data maintenance.

Question 17

Outline the levels of normalization (up to 5NF):

Answer 17

• 1NF: Eliminate duplicate columns and ensure atomicity.
  
  • 2NF: Remove partial dependencies.
  • 3NF: Remove transitive dependencies.
  • BCNF: Strengthen 3NF to handle overlapping candidate keys.
  • 4NF: Eliminate multi-valued dependencies.
  • 5NF: Handle join dependencies.

Question 18

Produce Logical Design Model

Answer 18

1. Identify and create relations for strong and weak entities.
   
   • Strong entities have independent existence (e.g., Customer), while weak entities depend on others (e.g., Order depends on Customer).
2. Map multivalued attributes.
   
   • Convert attributes with multiple values (e.g., Phone numbers) into separate tables.
3. Map binary relations:
   
   • One-to-one: A customer has one unique profile.
   • One-to-many: A customer can place many orders.
   • Many-to-many: Many customers can buy many products.
4. Map ternary relations.
   
   • Handle relationships involving three entities (e.g., Supplier supplies Product to Customer).
5. Map supertype relationships.
   
   • Represent inheritance (e.g., Employee as a supertype with subtypes like Manager and Staff).
6. Validate integrity constraints:
   
   • Domain constraints: Ensure data falls within valid ranges (e.g., Age > 0).
   • Entity integrity constraints: Ensure primary keys are unique and not null.
   • Referential integrity constraints: Ensure foreign keys reference valid primary keys.
7. Create a data dictionary.
   
   • Document all entities, attributes, relationships, and constraints for reference