Data Analysis and Database Design

Introduction

Information systems depend on data that is accurate, understandable, accessible, and properly controlled. Data analysis in systems development is the disciplined examination of an organization’s information requirements, business rules, data structures, and data flows. Database design translates that analysis into a logical and, eventually, physical structure that can store and retrieve data reliably.

The central design challenge is not simply deciding where to place fields in tables. It is determining what the organization needs to remember, how facts relate to one another, which rules must always be true, and how data can remain useful as the system changes. Poor data design produces duplication, inconsistent reports, update anomalies, weak security, and costly maintenance. Good design creates a shared representation of organizational knowledge.

This chapter examines conceptual and logical data modelling, entities, attributes, relationships, cardinality, keys, normalization, metadata, data dictionaries, data quality, and governance. It also considers how these ideas support implementation and professional decision-making.

Learning Objectives

By the end of this chapter, a student should be able to:

1. Explain the role of data analysis in systems analysis and design.
2. Identify entities, attributes, relationships, business rules, and constraints.
3. Construct and interpret conceptual and logical data models.
4. Select suitable primary, candidate, foreign, natural, and surrogate keys.
5. Apply first, second, and third normal forms to relational designs.
6. Explain metadata and develop a useful data dictionary.
7. Evaluate data quality and propose governance controls.
8. Translate a logical model into an implementable relational database design.

1. Data Analysis as a Systems Development Activity

Data analysis begins with questions such as:

• What information does the organization create, receive, store, use, and share?
• Which real-world objects or events must be represented?
• What facts describe those objects and events?
• How are the objects related?
• Which rules constrain valid data?
• Who owns, uses, protects, and approves the data?

Data analysis differs from data entry and database administration. Data entry records values; database administration operates and protects databases; data analysis determines the meaning and structure of the data required by the system.

A systems analyst gathers data requirements through interviews, workshops, document analysis, observation, questionnaires, existing database inspection, and process modelling. Requirements should be expressed in business language before they are converted into technical structures. For example, “a customer may place many orders, but each order belongs to one customer” is a business rule. It can later be represented through entities, relationships, and cardinality.

Data analysis is closely connected with process analysis. Processes use and produce data. A sales process may read customer and product data, create an order, and produce an invoice. A process model describes activities and flows; a data model describes the stable information structures needed by those activities.

2. The Data-Modelling Perspective

A data model is a formal representation of data objects, their properties, and their relationships. Three levels are commonly distinguished.

2.1 Conceptual data model

The conceptual model provides a high-level, technology-independent view. It identifies major entities and relationships without specifying database tables, indexes, or storage types. It is intended for communication among users, managers, analysts, and designers.

For a university enrolment system, conceptual entities might include Student, Course, Instructor, and Enrollment. The model may state that a student enrolls in courses and an instructor teaches courses, but it need not yet specify how these relationships are implemented.

2.2 Logical data model

The logical model translates the conceptual model into a more precise structure. It identifies attributes, primary keys, foreign keys, relationship resolution, domains, and integrity constraints. In a relational design, entities generally become relations or tables, although the design is still independent of a particular database product.

A many-to-many relationship between Student and Course is represented logically by an associative entity such as Enrollment. Enrollment may contain StudentID, CourseID, EnrollmentDate, and Grade.

2.3 Physical data model

The physical model specifies implementation details such as data types, indexes, partitions, storage arrangements, naming conventions, and vendor-specific constraints. Physical design must consider performance, security, scalability, availability, and cost.

The three levels are related but should not be confused. A conceptual model communicates meaning; a logical model defines structure; a physical model optimizes implementation.

3. Entities, Attributes, and Domains

3.1 Entities

An entity is a distinguishable real-world object, event, place, role, or concept about which the organization needs to store data. Examples include Customer, Product, Employee, Payment, Appointment, and Department.

An entity type is a category of similar objects, while an entity instance is one occurrence of that type. “Customer” is an entity type; customer number C1042 is an instance. Analysts should avoid identifying entities merely because a noun appears in a conversation. An entity is justified when it has a distinct identity, relevant properties, and a reason to be stored or referenced.

Entities may be:

• Strong entities, which have their own identifying key.
• Weak or dependent entities, whose identification depends partly on another entity.
• Associative entities, which resolve many-to-many relationships and often record facts about an association.
• Transaction or event entities, which record occurrences such as orders, payments, or admissions.

3.2 Attributes

An attribute is a property that describes an entity or relationship. Customer may have CustomerID, Name, EmailAddress, and RegistrationDate. An attribute should have a clear meaning, an appropriate domain, and a rule determining whether it is mandatory.

Attributes can be classified as follows:

The distinction between stored and derived data is important. Storing a derived value can improve performance or preserve historical results, but it creates a risk of inconsistency. If OrderTotal is stored, the system must control how it is recalculated when order lines change.

3.3 Domains

A domain defines the permitted values and meaning of an attribute. A domain may specify data type, length, format, range, precision, allowed codes, and nullability. For example, a Quantity domain may require a positive integer, while an EmailAddress domain may require a validated string format.

A domain is more than a technical type. Two attributes may both be stored as text but have different meanings and permitted values. Explicit domains improve validation, interoperability, and data quality.

4. Relationships and Cardinality

A relationship expresses an association between entity instances. A Customer places an Order; an Employee belongs to a Department; a Product appears on an OrderLine.

Relationships are described by:

• Name: a meaningful verb or verb phrase.
• Degree: the number of participating entity types, such as unary, binary, or ternary.
• Cardinality: the maximum number of related instances.
• Optionality or participation: whether participation is mandatory or optional.

Common cardinalities include:

Cardinality must be derived from business rules, not guessed from current data. If every employee currently has one office, that does not prove that an employee can never have multiple offices. Analysts should ask about policy, exceptions, history, and future requirements.

Optionality is equally important. A department may have zero or many employees, while each employee may be required to belong to exactly one department. This can be written as Department 1 —— 0..* Employee and Employee 1 —— 1 Department, using a notation in which the lower bound indicates minimum participation and the upper bound indicates maximum participation.

A many-to-many relationship cannot normally be implemented directly in a relational table without repeating groups or awkward structures. It is resolved using an associative entity. For example:

• Student(StudentID, Name)
• Course(CourseID, Title)
• Enrollment(StudentID, CourseID, EnrollmentDate, Grade)

Enrollment represents the fact that a particular student takes a particular course. Its composite primary key may be (StudentID, CourseID), unless the institution permits repeated enrolment in the same course and therefore requires an additional attempt identifier.

5. Keys and Integrity Constraints

A key is an attribute or combination of attributes used to identify records or establish relationships.

• A superkey is any attribute set that uniquely identifies a row.
• A candidate key is a minimal superkey.
• A primary key is the candidate key selected as the principal identifier.
• An alternate key is a candidate key not selected as the primary key.
• A foreign key is an attribute or attribute set that references a key in another relation.
• A composite key contains more than one attribute.
• A natural key has meaning in the business domain, such as a national registration number.
• A surrogate key is an artificial identifier, such as an automatically generated numeric or universally unique identifier.

A primary key should be unique, stable, minimal, and always available. Natural keys can be meaningful but may change, be lengthy, or expose sensitive information. Surrogate keys simplify relationships and protect against changes in business identifiers, but they do not remove the need for business uniqueness constraints. For example, a surrogate CustomerID may identify a customer while a unique constraint still prevents duplicate combinations of approved email and organization code where appropriate.

Integrity constraints include:

1. Entity integrity: a primary key is unique and not null.
2. Referential integrity: a foreign-key value must refer to an existing parent key or be null when the relationship is optional.
3. Domain integrity: values must conform to defined domains.
4. Business integrity: organizational rules must be enforced, such as an invoice date not preceding its order date.

Constraints should be implemented as close to the data as practical, but application validation remains useful for clear user feedback and complex rules.

6. Relational Design and Normalization

The relational model represents data as relations, commonly implemented as tables. A relation consists conceptually of attributes and tuples, although practical database systems add indexes, constraints, views, and physical features.

Normalization is a systematic technique for reducing unnecessary redundancy and preventing anomalies. It is based on dependencies among attributes. Normalization is not a universal command to split every table as far as possible; designers must also consider performance, reporting, historical requirements, and controlled denormalization.

6.1 Functional dependency

A functional dependency exists when one attribute or attribute set determines another. If each ProductID identifies exactly one ProductName, then ProductID functionally determines ProductName, written as ProductID → ProductName. This statement depends on a business rule, not merely on observed values.

6.2 First normal form

A relation is in first normal form (1NF) when each field contains an atomic value for the purposes of the application and repeating groups are removed. A row should not contain a list such as “red, blue, green” in one column if those values need to be separately searched, validated, or related.

Instead of Customer(CustomerID, Name, Phone1, Phone2), a separate CustomerPhone relation can store one phone number per row:

CustomerPhone(CustomerID, PhoneNumber, PhoneType)

Atomicity is context-dependent. An address may be stored as one value when the application treats it as an uninterpreted postal label, but it should be decomposed when the system must search by city or postal code.

6.3 Second normal form

A relation is in second normal form (2NF) when it is in 1NF and every non-key attribute depends on the whole primary key, not merely part of a composite key. 2NF is relevant when a relation has a composite key.

Consider:

OrderLine(OrderID, ProductID, ProductName, Quantity, UnitPrice)

Assume the key is (OrderID, ProductID). ProductName depends only on ProductID, not on the whole key. This partial dependency causes duplication. ProductName belongs in Product, while Quantity belongs in OrderLine. UnitPrice requires careful analysis: if it is the current product price, it belongs to Product; if it is the price charged at the time of sale, it belongs to OrderLine because it is a historical fact about that line.

6.4 Third normal form

A relation is in third normal form (3NF) when it is in 2NF and has no transitive dependency of a non-key attribute on the primary key through another non-key attribute.

For example:

Employee(EmployeeID, EmployeeName, DepartmentID, DepartmentName)

EmployeeID → DepartmentID and DepartmentID → DepartmentName. Therefore DepartmentName depends transitively on EmployeeID. It should be stored in Department, not repeatedly in Employee.

A practical summary is that each non-key fact should describe the key, the whole key, and nothing but the key. This is a useful design heuristic, though formal normalization analysis should consider all candidate keys and dependencies.

6.5 Higher normal forms and denormalization

Boyce–Codd normal form (BCNF) is stricter than 3NF and requires every determinant to be a candidate key. Fourth and fifth normal forms address certain multivalued and join dependencies. These are important in specialized designs but are less frequently required in introductory operational systems.

Denormalization intentionally introduces controlled redundancy to improve read performance, simplify reporting, or preserve snapshots. It should be justified by measured requirements and supported by mechanisms that maintain consistency. A data warehouse, for example, may use dimensional structures that are less normalized than an operational transaction database.

7. Worked Example: Normalizing an Order Relation

Assume an initial spreadsheet contains:

OrderRecord(OrderID, OrderDate, CustomerID, CustomerName, ProductID, ProductName, Quantity, SalePrice)

One row represents one product on an order. The assumed dependencies are:

• OrderID → OrderDate, CustomerID
• CustomerID → CustomerName
• ProductID → ProductName
• (OrderID, ProductID) → Quantity, SalePrice

The composite key is (OrderID, ProductID), assuming a product appears at most once on an order.

Step 1: 1NF. Each row contains one product occurrence and atomic values, so the relation is in 1NF.

Step 2: Remove partial dependencies for 2NF. OrderDate and CustomerID depend only on OrderID. ProductName depends only on ProductID. Decompose into:

• Order(OrderID, OrderDate, CustomerID)
• Product(ProductID, ProductName)
• OrderLine(OrderID, ProductID, Quantity, SalePrice)

Step 3: Remove the transitive dependency for 3NF. CustomerName depends on CustomerID, not directly on OrderID. Create:

• Customer(CustomerID, CustomerName)
• Order(OrderID, OrderDate, CustomerID)
• Product(ProductID, ProductName)
• OrderLine(OrderID, ProductID, Quantity, SalePrice)

Order.CustomerID is a foreign key to Customer, while OrderLine.OrderID and OrderLine.ProductID are foreign keys to Order and Product. SalePrice remains in OrderLine because it records the price actually charged, which may differ from the current Product price.

This design prevents changing a customer’s name in hundreds of order rows and avoids confusing current price with historical sale price.

8. Metadata and Data Dictionaries

Metadata is data about data. It explains meaning, structure, origin, ownership, quality, permitted use, and technical representation. Metadata may be:

• Business metadata: business definition, owner, sensitivity, and approved use.
• Technical metadata: table name, column name, data type, length, index, and relationship.
• Operational metadata: creation time, update time, source system, processing status, and lineage.
• Governance metadata: retention period, classification, access rules, and stewardship responsibility.

A data dictionary is a controlled repository describing data elements and their relationships. A useful entry might include:

A data dictionary supports communication, reduces ambiguity, assists testing, facilitates integration, and provides evidence for governance and audit. It should be version-controlled. Definitions should be reviewed when business processes, regulations, or systems change.

9. Data Quality and Governance

A database can be structurally well designed and still contain poor data. Data quality concerns whether data is fit for its intended use. Common dimensions include:

• Accuracy: correctly represents the real-world object or event.
• Completeness: required values are present.
• Consistency: the same fact does not conflict across locations.
• Timeliness: data is available and current when needed.
• Validity: values conform to definitions and rules.
• Uniqueness: duplicate records are controlled.
• Integrity: relationships and constraints remain valid.
• Accessibility: authorized users can obtain data in usable form.

Quality should be managed across the data life cycle: creation, validation, use, sharing, archival, and disposal. Prevention is usually better than repeated cleansing. Examples include controlled code lists, mandatory fields, reference-data management, duplicate detection, source validation, and reconciliation procedures.

Data governance is the framework through which an organization assigns authority and accountability for data. Governance commonly defines:

• Data owners who make policy and access decisions.
• Data stewards who maintain definitions and quality rules.
• Custodians or administrators who operate technical controls.
• Standards for naming, modelling, classification, retention, and sharing.
• Processes for issue resolution, change approval, and audit.

Governance must be proportional to risk. Customer identity, health, financial, and educational records require stronger controls than non-sensitive public information.

10. Security, Privacy, and Ethical Considerations

Database design has ethical and legal consequences. Analysts should apply privacy principles such as purpose limitation, data minimization, accuracy, access control, transparency, retention limitation, and accountability. Applicable laws vary by jurisdiction, but professional design should identify relevant requirements rather than assume that technical capability creates permission to collect or use data.

Important controls include:

• Role-based access according to least privilege.
• Encryption in transit and, where appropriate, at rest.
• Secure authentication and separation of duties.
• Audit logs for sensitive access and changes.
• Masking or tokenization in test environments.
• Retention and secure deletion procedures.
• Backup protection and disaster recovery.
• Procedures for correcting data and responding to incidents.

A data model may expose sensitive relationships even when individual attributes seem harmless. For example, joining location, timestamp, and service records may identify a person. Analysts should therefore assess re-identification risk and avoid collecting data solely because it might become useful later.

11. Applied Case: University Course Enrolment System

The following is an illustrative case.

Background

A university uses spreadsheets to record students, courses, instructors, and enrolments. Different departments use different course codes and maintain local copies of student contact details.

Problem

The university experiences duplicate students, inconsistent course names, missing grades, and difficulty determining class capacity. A student who changes an email address may have different addresses in different files. Reports require manual reconciliation.

Analysis

Workshops identify these business rules:

1. Each student has one institutional identifier.
2. A student may enrol in many course sections.
3. A course may be offered in many academic terms.
4. A course offering, or section, is taught by one or more instructors according to departmental policy.
5. A student may receive at most one final grade for a particular section attempt.
6. A section has a capacity and cannot accept enrolments beyond that capacity.

The conceptual entities are Student, Course, Section, Instructor, and Enrollment. Course and Section must be distinguished: “Database Systems” is a course, while “Database Systems, Semester 2, Section A” is a particular offering.

Logical approach

A suitable logical structure is:

• Student(StudentID, Name, InstitutionalEmail)
• Course(CourseID, CourseCode, Title, Credits)
• Section(SectionID, CourseID, TermID, Capacity)
• Instructor(InstructorID, Name)
• SectionInstructor(SectionID, InstructorID)
• Enrollment(StudentID, SectionID, EnrollmentDate, Grade, Status)
• Term(TermID, Name, StartDate, EndDate)

Primary keys identify each entity. CourseID in Section, SectionID in SectionInstructor and Enrollment, and InstructorID in SectionInstructor are foreign keys. SectionInstructor resolves the many-to-many relationship between sections and instructors. Enrollment resolves the many-to-many relationship between students and sections and stores facts about that relationship.

Quality and governance approach

The university should establish a data dictionary with approved definitions for StudentID, CourseCode, Grade, and Status. A central student master should be authoritative for identity and contact information. Course codes should be governed by an academic registry. Validation should restrict Grade to approved values or a controlled grading domain, while capacity checks should occur within a transaction-safe process to prevent two simultaneous enrolments from exceeding capacity.

Outcome

The proposed design reduces duplication, supports consistent reporting, enables section-level capacity management, and clarifies responsibility. It also creates a foundation for analytics, provided that analytical use is governed and personally identifiable information is protected.

Learning points

The case demonstrates that database design begins with business rules. It also shows why events and associations deserve their own entities, why current and historical facts must be distinguished, and why governance is necessary in addition to normalization.

12. Contemporary Practices and Professional Implications

Modern systems may combine relational databases with document stores, graph databases, data lakes, cloud platforms, and application programming interfaces. The choice should follow data characteristics and workload requirements rather than fashion. Relational systems remain strong where transactions, integrity constraints, and structured relationships are central.

Artificial intelligence can assist with schema discovery, duplicate detection, documentation, and natural-language querying. However, generated models and classifications require human review. AI may infer incorrect relationships, reproduce bias in historical data, expose confidential information, or create definitions without organizational authority. Governance should require traceability, validation, access controls, and accountability for AI-assisted decisions.

Employability depends on more than drawing diagrams. Graduates should be able to elicit business rules, explain trade-offs to non-technical stakeholders, write clear definitions, inspect data quality, use SQL appropriately, document assumptions, and recognize privacy and security implications. They should also understand that a data model is a communication artifact and a control mechanism, not merely a technical diagram.

13. Best-Practice Checklist

Before approving a logical database design, ask:

1. Are all important business objects, events, and associations represented?
2. Does every entity have a clear definition and stable identifier?
3. Are relationship cardinalities and optionalities supported by documented rules?
4. Are many-to-many relationships resolved appropriately?
5. Are domains, nullability, and validation rules explicit?
6. Are primary and foreign keys justified?
7. Has the design been checked for partial and transitive dependencies?
8. Are historical facts distinguished from current reference values?
9. Are ownership, sensitivity, retention, and access rules documented?
10. Have quality, performance, integration, and future change been considered?

Chapter Summary

Data analysis identifies the information that a system must manage and the rules governing it. Data modelling expresses that understanding at conceptual, logical, and physical levels. Entities represent distinguishable objects or events; attributes describe them; relationships show associations; and cardinality and optionality express business constraints.

Keys provide identity and connect relations through foreign keys. Entity, referential, domain, and business integrity protect valid data. Normalization reduces redundancy and update anomalies by organizing facts according to dependencies. First normal form removes repeating groups, second normal form removes partial dependencies, and third normal form removes transitive dependencies. Controlled denormalization may be appropriate when supported by clear performance or historical requirements.

Metadata and data dictionaries make data understandable and governable. Data quality includes accuracy, completeness, consistency, timeliness, validity, uniqueness, integrity, and accessibility. Governance assigns ownership and establishes standards, while privacy and security controls protect people and organizations. Effective database design therefore combines technical structure with business understanding, ethical responsibility, and continuous data management.

Key Terms

Entity: A distinguishable object, event, role, or concept about which data is stored.

Attribute: A property that describes an entity or relationship.

Domain: The permitted values, meaning, and constraints of an attribute.

Relationship: An association between entity instances.

Cardinality: The maximum number of instances that may participate in a relationship.

Optionality: The minimum participation allowed in a relationship.

Primary key: The selected candidate key that uniquely identifies each row.

Candidate key: A minimal attribute set that uniquely identifies a row.

Foreign key: An attribute that references a key in another relation.

Surrogate key: An artificial identifier generated for database use.

Natural key: An identifier with established meaning in the business domain.

Associative entity: An entity used to represent a many-to-many relationship.

Functional dependency: A rule in which one attribute set determines another.

Normalization: The process of organizing relations to reduce redundancy and anomalies.

Metadata: Data that describes the meaning, structure, origin, or use of data.

Data dictionary: A controlled repository of definitions and technical details about data elements.

Data quality: The fitness of data for its intended use.

Data governance: Authority, accountability, standards, and processes for managing data.

Referential integrity: The requirement that foreign-key references remain valid.

Denormalization: The deliberate introduction of redundancy for a justified purpose.

Review Questions

5 Short Answer Questions

1. Distinguish between a conceptual data model and a logical data model.
2. What is the difference between cardinality and optionality?
3. Define a foreign key and explain its role in referential integrity.
4. Why might SalePrice belong in OrderLine rather than Product?
5. Identify four dimensions of data quality.

5 Long Answer Questions

1. Explain the role of entities, attributes, relationships, and domains in a relational database design.
2. Describe primary, candidate, alternate, natural, surrogate, composite, and foreign keys, including the advantages and limitations of surrogate keys.
3. Explain first, second, and third normal forms using an order-processing example.
4. Discuss how a data dictionary supports systems development, integration, testing, and governance.
5. Examine the relationship between database design, privacy, security, and ethical responsibility.

3 Analytical / Application Questions

1. A clinic stores PatientID, PatientName, DoctorID, DoctorName, AppointmentDate, and Diagnosis in one table. Identify likely dependencies, propose a normalized design, and state assumptions about appointments and doctors.
2. Design a conceptual and logical model for a library in which members borrow books, books have multiple copies, and each loan records issue and return dates. Explain keys, cardinalities, and important constraints.
3. A retail organization wants to combine customer transactions from three systems. Develop a data-quality and governance plan addressing duplicates, conflicting identifiers, ownership, privacy, validation, and ongoing monitoring.

Multiple Choice Questions with Answers

Question 1

Which model primarily communicates major entities and relationships without implementation details?

A. Conceptual data model

B. Physical data model

C. Backup model

D. User-interface model

Correct Answer: A. Conceptual data model

Question 2

Which structure normally resolves a many-to-many relationship between Student and Course?

A. A repeated CourseName field in Student

B. An associative Enrollment entity

C. A single text field containing course codes

D. A second copy of the Student table

Correct Answer: B. An associative Enrollment entity

Question 3

Which key is selected as the principal identifier of rows in a relation?

A. Foreign key

B. Alternate key

C. Primary key

D. Composite attribute

Correct Answer: C. Primary key

Question 4

In the dependency ProductID → ProductName, what does the dependency state?

A. ProductName uniquely identifies ProductID

B. ProductID determines ProductName

C. Both attributes must be foreign keys

D. ProductID must contain several values

Correct Answer: B. ProductID determines ProductName

Question 5

Which normal form removes partial dependencies on part of a composite key?

A. First normal form

B. Second normal form

C. Third normal form

D. Fifth normal form

Correct Answer: B. Second normal form

Question 6

In the relation Employee(EmployeeID, DepartmentID, DepartmentName), assuming DepartmentID determines DepartmentName, what problem exists?

A. A transitive dependency

B. A missing repeating group

C. A violation caused by a null primary key only

D. A many-to-many relationship

Correct Answer: A. A transitive dependency

Question 7

Which item is an example of business metadata?

A. Column storage length only

B. Index page size

C. Approved definition and data owner

D. CPU utilization

Correct Answer: C. Approved definition and data owner

Question 8

Which data-quality dimension asks whether a value correctly represents the real-world fact?

A. Accuracy

B. Timeliness

C. Accessibility

D. Uniqueness

Correct Answer: A. Accuracy

Question 9

What is the main purpose of a foreign key?

A. To encrypt a table

B. To enforce a relationship with a referenced key

C. To calculate derived attributes

D. To replace every candidate key

Correct Answer: B. To enforce a relationship with a referenced key

Question 10

Which practice best reflects least privilege?

A. Give every employee full database access

B. Allow access according to the user’s authorized responsibilities

C. Store passwords in a public data dictionary

D. Disable audit logging to improve performance

Correct Answer: B. Allow access according to the user’s authorized responsibilities