π Lecture 6 β Process to Database Design
π Overview: This lecture covers the complete process of database design from the perspective of requirements engineering. It explains how to gather, analyze, specify, and verify system requirements, emphasizing that requirements form the foundation of any successful database system. Understanding this process is critical because poorly defined requirements lead to system failures, cost overruns, and user dissatisfaction.
ποΈ Topics Covered
The lecture begins with motivation for requirements gathering and major challenges including missing, wrong, and changing requirements. It then covers the attributes of software requirements: verifiability, completeness, consistency, and traceability. The requirement engineering discipline is introduced, divided into requirement management and requirement development, which includes elicitation, analysis, specification, and verification. Later sections address project business requirements, user involvement, quality metrics (reliability, robustness, user friendliness), requirement prioritization, and the distinction between defects and bugs.
π Lecture Summary
A. Motivation for Requirements
The requirement gathering process identifies the actual need, purpose, or potential update need of a system, and these requirements form the foundation of system design and development. The process is non-technical and involves communication of requirements. A requirement is defined as the conditions or capabilities to which a system must conform, related to functionality, usability, reliability, performance, or supportability.
TT. Major Challenges in Requirement
Three major challenges exist in requirement gathering: Missing Requirements occur when the person gathering requirements misses important ones due to human error, poor observation, poor analysis, or the interviewee's inability to explain facts properly. Wrong Requirements happen when requirements are wrongly communicated, exhibited, or misinterpreted during gathering and documentation. Changing Requirements occur over time β changes can happen in data, data flow, relationships, end-user features, or delivery schedule, disturbing the development process.
UU. Attributes of Software Requirements: - Verifiable
A requirement must be communicated so the testing team can verify it. A requirement is verifiable if there is a process through which a person or machine can check that the software product meets the requirement.
π Example: The requirement "Loading time should be as minimum as possible" is not verifiable because "minimum" is undefined. Solution: "Loading time should be 2 seconds."
VV. Attributes of Software Requirements: - Completeness
A complete requirements specification must precisely define all real-world situations that will be encountered and the system's responses to them. It must not include subjective, non-verifiable, or non-specific situations β there is no room for guesswork.
π Example: The requirement "On Power Loss, backup power should support Normal Operations" is incomplete because "Normal Operations" is subjective. Solution: "On Power Loss, backup power should support Normal Operations for 30 minutes."
WW. Attributes of Software Requirements: - Consistent
A requirement is consistent if it is not in conflict with any other existing requirements. Conflict may arise from variation in language patterns, different vocabulary, or multiple descriptions of a single requirement. Vagueness in requirements is the quality destroyer.
π Example: Requirement 01: "The customer support should be ITIL compliant." Requirement 02: "The network support should be ISO-ITIL 3.0 compliant." Question: Are ITIL and ITIL-3.0 the same or different? Implication: No consistency. Solution: Unify the terminology to either both be ITIL 3.0 compliant.
XX. Attributes of Software Requirements: - Traceability
A requirement is traceable if both the origins and the references of the requirements are available. Traceability of origin helps understand what modifications were made to bring the requirement to its current state. Traceability of references aids modification of future documents by stating where a requirement has been referenced. Making requirements traceable is also useful in coding and testing.
π Example: "System must generate a batch report when batch is completed and a discrepancy report when batch is aborted." Issue 01: How uniquely can you identify the batch report? Issue 02: What if requirement changes and discrepancy report is not required? Implication: Zero traceability. Solution: Assign unique requirement numbers to each.
YY. Requirement Engineering Discipline
Requirements engineering emphasizes the use of systematic and repeatable techniques that ensure the completeness, consistency, and relevance of system requirements. It acts as the bridge between the real-world needs of users/customers and the capabilities afforded by software. Requirements engineering is divided into requirement management and requirement development, which is further divided into elicitation, analysis, specification, and verification.
ZZ. Requirement Management
Requirements management is the process of scheduling, coordinating, and documenting the requirements engineering activities (elicitation, analysis, specification, and verification). This discipline takes into account different strategies to keep the project scope within the baseline. It monitors and controls the project scope and provides a mechanism to handle proposed changes while communicating impacts to relevant stakeholders.
π‘ Why this matters: Without proper requirement management, scope creep can cause projects to fail completely.
AAA. Example of Requirement Management
π Example: Baseline Requirement A defined on 22-May-2015. Development proceeds as per A until 2-June-2015. Client changes requirement to B on 10-June-2015. Demo Day is 15-June-2015. Client says: "This is not what I was expecting." Reason: Client is referring to requirement B, but development team is still working on requirement A.
BBB. Requirement Development (RD)
Requirement development is the part of requirement engineering where requirements are gathered and turned into specifications of what the system must do, or the scope/features of the software system. RD activities include Elicitation, Analysis, Specification, and Verification. While defining project scope, RD specifically identifies and communicates what not to deliver. The artifact of RD process is the baseline requirements (defined schedule).
CCC. Requirement Elicitation or Gathering
Requirement elicitation is about gathering the system's requirements using fact-finding techniques (covered in Lesson 04). The aim is to apply all techniques to get the real requirements of the client. One technique alone is not sufficient β more than one technique must be used to eliminate limitations. Techniques include interview, questionnaire, observation, or any mixture as needed.
DDD. Project Business Requirement
There must be a baseline for scope in order to manage scope properly. The priority of scope must be defined along with the baseline to deliver. This prioritization helps the development team schedule activities to deliver the project scope on time. This defines and communicates the product roadmap.
EEE. Example of Project Business Requirement
π Product Roadmap: Total scope = 20 Features. High Priority = 8 (agreed by client and PM). Medium Priority = 7. Normal Priority = 5. Release 1: 8 Features by Date X. Release 2: 8 + 7 Features by Date Y (medium priority included). Release 3: 8 + 7 + 5 Features by Date Z (all features delivered).
FFF. Get User Involvement
Lack of user input can make a project very challenging. User involvement is of utmost importance at every stage of system development. The concept of Product Champion is worth mentioning β this is the person officially responsible for the delivery of the system. The product champion helps stakeholders and the project manager reach a shared vision, defines and initiates the product within that vision. The product champion must have technical competence, knowledge about the company and markets, and should capture as much knowledge as possible so that if the client's representative leaves, valuable knowledge doesn't leave with them.
GGG. Define Quality Metrics
A metric is a measure, and combining it with quality means the measures of quality. Quality is defined by customers β if the system provides what the customer needs, it can be called a quality system. Quality metrics focus on non-functional requirements that specify criteria used to judge the operation of a system. Broadly, functional requirements define what a system is supposed to do, while non-functional requirements define how a system is supposed to be. Key quality metrics include: Reliability, Robustness, and User Friendliness.
HHH. Example of Quality Metric β Reliability
Reliability is the capacity of the designed/developed system to perform as required over time. It is also defined as resistance to failure over time. Reliability is all about stability of the system.
π Example: A program for a Web server has a failure intensity objective of 1 failure per 100,000 transactions. During testing, the program runs for 50 hours, handling 10,000 transactions per hour on average with no failures occurring.
III. Example of Quality Metric β Robustness
Robustness is the ability of a computer system to cope with errors during execution. Robustness can also be defined as the ability of an algorithm to continue operating despite abnormalities in input, calculations, etc. The check for this metric is: Does the system crash on abnormal inputs?
π Example: How does the algorithm behave in case of abnormal input? If user inputs character "A" instead of a numeric value, does the system crash?
JJJ. Example of Quality Metric β User Friendliness
User Friendliness is synonymous with usability and is defined as the ease of use or the learnability of the developed system. User friendliness is the extent to which the system can be used by the end user to get desired results with effectiveness and ease of use. Most clients reject software based on interfaces that are not user friendly.
π Example: For color scheme of the software β first ask the client for the color theme, get the color code or sample image, then use mockups of that image.
KKK. Requirement Analysis
After gathering requirements, the next step is to analyze them based on the attributes covered earlier (verifiability, completeness, consistency, traceability). This stage breaks down functional and non-functional requirements to a basic design view to provide a clear system development process framework. Requirement analysis involves various entities including business, stakeholders, and technology requirements. This stage also identifies conflicts with the baseline as prototypes are made.
LLL. Requirement Prioritization
At this stage, the priority of requirements is defined in coordination with the development team and the client, based on statistics and facts. The limitations of the project are also identified here. Prioritization helps in stage-release of the system, where development is divided into stages based on priority. The approach is to categorize requirements into High, Medium, and Low on some statistical basis.
MMM. Requirement Specification
Requirement specification is the result of requirement analysis. It establishes the basis for agreement between clients and the development team on what the software product is to do and what not to do. Requirements are further classified into functional and non-functional categories and recorded in a mutually agreed document called the Software Requirement Specification (SRS). This document enlists enough and necessary requirements for project development.
NNN. Requirements Verification
The correctness and completeness of system requirements is verified at this stage to ensure the system in manufacturing will meet user needs. Verification is the process of checking that the software meets the specification. The goal is to ensure requirements are good enough to proceed to design phase and development phase. This stage consists of thorough inspection because defect and bug fixing later costs too much.
π Definition β Defect: The wrong definition of the client's requirements. A defect is not something that is not working β it is simply working but is an unrequired part of the system. This can be detected in production phase.
π Definition β Bug: Noncompliance with the agreed behavior β software is not doing what it is supposed to do. This is a deviation from the requirements of the system.
π‘ Why this matters: Defects and bugs caught early in requirements verification cost significantly less to fix than those found during or after development.
β Key Takeaways
The most critical concept from this lecture is that requirements must possess four essential attributes: verifiability, completeness, consistency, and traceability β without these, the database/system design will fail. Students must understand the distinction between requirement management (scheduling, coordinating, documenting) and requirement development (elicitation, analysis, specification, verification). The Software Requirement Specification (SRS) is the key deliverable that establishes agreement between clients and developers. Quality metrics (reliability, robustness, user friendliness) focus on non-functional requirements and define how a system should perform, not just what it should do. Finally, defects (unrequired features that work) must be distinguished from bugs (failures to meet agreed behavior), and both must be caught early through thorough verification.
π§ Quick Revision Questions
-
What are the four essential attributes of software requirements, and why is each important for database design?
-
How does requirements engineering act as a bridge between real-world needs and software capabilities?
-
What is the difference between requirement management and requirement development?
-
Define defect versus bug β provide an example of each from the lecture.
-
Explain the concept of "Product Champion" and why user involvement is critical throughout system development.
π Lecture 7 β Relational Database
π Overview: This lecture introduces the relational database model based on E.F. Codd's foundational work. It covers all 12 of Codd's rules that define what constitutes a true relational database, along with essential concepts like database schemas, keys, and the four main subsets of SQL used for defining, manipulating, controlling, and retrieving data.
ποΈ Topics Covered
The lecture systematically explains Codd's 12 rules for relational databases (Information Rule through Non-Subversion Rule), then covers database schemas as logical groupings of database objects with examples, introduces keys for unique record identification, and concludes with the four SQL subsets: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Data Retrieval Language (DRL).
π Lecture Summary
Database Modeling and Design VU
A relational database is defined as a database whose organization is based on the relational model of data, where all data is presented in the form of tuples (records or rows) which are grouped into relations. The relational model was first proposed by E. F. Codd in his seminal 1970 paper "A relational model of data for large shared data banks." Codd proposed a set of rules that any database must follow to be called a relational database.
A. Codd Rule No. 01: The Information Rule
"All information in the relational database is represented in exactly one and only one wayβby values in tables." This means all information (data or Metadata) must be stored in a table format following the row and column format. Every piece of data permanently stored in a database is located in a table.
π Definition β Metadata: Data about data; the information that describes the structure and organization of the database itself.
B. Codd Rule No. 02: Guaranteed Access Rule
"Each and every datum (atomic value) is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column name." This rule emphasizes the importance of primary keys in locating data. The table name locates the correct table, the column name finds the correct column, and the primary key value finds the row containing the individual data item of interest.
π Definition β Primary Key: A unique identifier for each row in a database table that guarantees logical access to individual data items.
C. Codd Rule No. 03: Systematic Handling of Null
"NULL values (distinct from empty character string or a string of blank characters and distinct from zero or any other number) are supported in the fully relational RDBMS for representing missing information in a systematic way, independent of data type." NULL cannot be considered as zero or empty β it represents something unknown or missing. NULL must be given a systematic and uniform treatment and can be interpreted as data is missing, data is not known, or data is not applicable.
π Example:
- Question: How many books does Ali have?
- Answer: May be 5 or not known (null)
- In database column representation, "not known" will use NULL, not any numeric value
D. Codd Rule No. 04: Active Online Catalogue
"The structure description of the entire database must be stored in an online catalog, known as data dictionary, which can be accessed by authorized users." Users can use the same query language to access the catalog as they use to access the database itself. The system must support an online, inline, relational catalog accessible to authorized users via their regular query language.
π Definition β Data Dictionary: A centralized repository that stores the structure description of the entire database, accessible by authorized users.
E. Codd Rule No. 05: Powerful Language
"A database can only be accessed using a language having linear syntax that supports data definition, data manipulation, and transaction management operations." Linear syntax is a language parsed from left to right, implying the ability to write code without line-feed or carriage-return characters.
π Example of Power Language:
- Linear Syntax:
Void main () { cout<< "Hello";}β C++ Language - Languages rely on semicolons and parentheses to separate code blocks
- Line feed or carriage return is not required to separate code blocks
F. Codd Rule No. 06: View Updating Rule
"All views that are theoretically updateable are also updateable by the system." This rule deals with views, which are virtual tables used to give various users different perspectives of the database structure. Views should be updateable without restriction, like tables. SQL has limitations β views with a single table and without GROUP BY clause are updateable only.
π Definition β View: A virtual table based on the result of a query, providing users with selected columns as per rights and privileges.
G. Codd Rule No. 07: Relational Level Operations
"Insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table." The system must support set-at-a-time insert, update, and delete operators, not limited to a single row. It must also support union, intersection, and minus operations to yield sets of data records.
H. Codd Rule No. 08: Physical Data Independence
"The way the data is stored physically must be independent of the logical manner in which it's accessed." Changes to the physical level (how data is stored in arrays or linked lists) must not require changes to applications based on the structure. Users shouldn't be concerned about how data is stored or accessed.
π‘ Why this matters: Physical data independence allows database administrators to change storage mechanisms without affecting applications or users.
I. Codd Rule No. 09: Logical Data Independence
"Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure." The logical data must be independent of the user's view. How a user views data should not change when the logical structure of the database changes. The business logic and storage mechanism work independently of how the user views the data.
π Example:
- Structure of Table β Employee (id, name, DOB)
- Application Program Access: name, id, DOB from Database
- Database Structure needs no change
J. Codd Rule No. 10: Integrity Independence
"A database must be independent of the application that uses it." All integrity constraints can be independently modified without needing changes in the application. Integrity rules should be stored in the data dictionary. Primary key constraints, foreign key constraints, check constraints, triggers, and so forth should all be stored in the data dictionary.
π Definition β Integrity Constraints: Rules that ensure data accuracy and consistency in the database, including primary keys, foreign keys, and check constraints.
K. Codd Rule No. 11: Distribution Independence
"The database language must enable application programs and terminal activities to remain logically unimpaired whether and whenever data are physically centralized or distributed." The database language must be able to manipulate data located on other computer systems. Users should always get the impression that data is located at one site only. This rule is regarded as the foundation of distributed database systems.
L. Codd Rule No. 12: Non-Subversion Rule
"If a relational system has or supports a low-level (single-record-at-a-time) language, that low-level language cannot be used to subvert or bypass the integrity rules or constraints expressed in the higher-level (multiple-records-at-a-time) relational language." There should be no backdoor to disable integrity and constraints. If low-level access is allowed, it must not bypass security or integrity rules.
π‘ Why this matters: This rule ensures that even privileged operations like backup or load utilities cannot bypass authentication, constraints, and locks.
PPP. Database Schema
A database schema is better explained as the group of database objects like tables, indexes, triggers, and constraints related to each other. It can be referred to as a blueprint of how the database is constructed or its structure. Schema is a logical way of grouping database objects like tables, indexes, and views. A single database can hold multiple schemas belonging to different users or applications. Schema groups tables by owner, implementing the first level of security.
QQQ. Database Schema Example
Consider an admin who creates a schema, creates a user, and sets a password. The user named "test" can create database objects and assign rights to other database users.
π Example:
- Admin Create Schema: Test (username)
- Admin assign Password: 123
- Admin grant privileges to Test
- Test: Login with password
- Test: create table, create constraints, create functions, create triggers β Database Objects
- Test can grant Privileges to other users β Database Users
RRR. Introduction to Keys
Each table in a database contains multiple rows, and each row is referred to as one database record. Each record or row is assigned a unique number to manage the record reference, which is known as a key. This key equips each row or database record with a unique identification that can be easily tracked.
SSS. Structured Query Language
SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a Relational Database Management System (RDBMS) and is considered a declarative language. It is composed of a set of commands enabling creating database and table structures, data manipulation, and administration. The subsets of SQL are:
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Data Retrieval Language (DRL)
TTT. Data Definition Language (DDL)
Data Definition Language (DDL) is a standard for commands that define the different structures in a database and is responsible for creating database objects. DDL statements create, modify, and remove database objects such as tables, indexes, and users. All objects are part of the data dictionary. Common DDL statements are CREATE, ALTER, and DROP.
- CREATE: To make a new database, table, index, user, or view
- ALTER: To modify an existing database object
- DROP: To destroy an existing database, table, index, or view
UUU. Data Manipulation Language (DML)
Data Manipulation Language (DML) is syntax similar to a computer programming language that is responsible for manipulating data in database tables. This language is used for selecting, inserting, deleting, and updating data in a database. DML statements work with data in tables and are applied at the table level. Common DML commands are:
- INSERT: To add new rows to a table
- UPDATE: To change values already in a table
- DELETE: To delete the rows in a table
XV. Data Control Language (DCL)
Data Control Language (DCL) is syntax similar to a computer programming language that is responsible for controlling access to the data stored in a database. It is used to create privileges allowing users access to and manipulation of the database. Two important DCL commands are GRANT (to give access rights) and REVOKE (to withdraw access rights). DCL also controls transactional processing with the following commands:
- COMMIT: To apply the transaction by saving database changes
- ROLLBACK: To undo all changes of a transaction
- SAVEPOINT: To divide the transaction into smaller sections, defining breakpoints for partial rollbacks
WWW. Data Retrieval Language (DRL)
Data Retrieval Language (DRL) is a command to retrieve data from a database object in the desired format. This is the most popular, flexible, and only way to retrieve data from a database. The command used is SELECT, which allows us to specify the type of information we want to retrieve.
β Key Takeaways
Codd's 12 rules define the theoretical foundation of relational databases, with critical rules including the Information Rule (all data in tables), Guaranteed Access Rule (primary key access), and Systematic Handling of NULL (unknown vs. zero/empty). Physical and Logical Data Independence (Rules 8 and 9) are essential for separating storage, structure, and user applications. A database schema serves as a blueprint grouping database objects by owner for security. SQL has four key subsets: DDL for structure definition (CREATE, ALTER, DROP), DML for data manipulation (INSERT, UPDATE, DELETE), DCL for access control (GRANT, REVOKE, COMMIT, ROLLBACK), and DRL for data retrieval (SELECT).
π§ Quick Revision Questions
- According to Codd's Rule 2 (Guaranteed Access Rule), what three elements must be combined to logically access any individual data item in a relational database?
- What is the difference between a NULL value and zero or an empty string in a relational database?
- How does Physical Data Independence (Rule 8) differ from Logical Data Independence (Rule 9)?
- What are the four SQL subsets, and what specific commands does each include?
- What is a database schema, and how does it implement the first level of database security?
π Lecture 8 β Conceptual Data Modeling and Entity Relationship Diagram Overview
π Overview: This lecture introduces Entity Relationship Diagrams (ERDs), a foundational tool for relational database design developed by Peter Chen in 1976. It explains the rationale behind ERDs, their core components, and the step-by-step process for creating them, making it essential for visually representing business data and its relationships.
ποΈ Topics Covered
The lecture covers the concept and rationale of Entity Relationship Diagrams (ERDs), followed by a detailed explanation of the three main components: Entity, Attributes, and Relationships. It concludes with a systematic process to create an ERD, including identifying entities, defining relationships, and determining cardinalities.
π Lecture Summary
A. Concept of Entity Relationship Diagram (ERD)
Entityβrelationship modeling was developed by Peter Chen and published in 1976; it serves as the building block of relational database design. An Entity Relationship Diagram (ERD) is a graphical representation of the relationships between data in a database. It is the result of using a systematic process and it only visualizes the business data instead of defining the business process. In very simple terms, ERD is a visual representation of data that describes how the data is related to each other.
XXX. Rationale for ERD
It is the first diagram in database design which gives a higher level description of the system and actually visualizes the system requirements. It is probably the easiest way to describe the interaction or the relation between the different components by using different notations and to get a picture of functionality needed for the system. The data is represented as components (entities) that are linked with each other by relationships that express the dependencies and requirements between them, thus making them easy to understand.
YYY. Components of ERD
There are three main components of ERD:
- Entity
- Attributes
- Relationships
ZZZ. Entity & Attributes
The word entity is rooted from the Latin word βenβ which means being. Entity is the name of a place, person, or thing about which something can be stored in a system. An entity can be a real-world object that can be easily identifiable. For example, in a school database, students, teachers, classes, and courses offered can be considered as entities. All these entities have some attributes or properties that give them their identity. An entity set is a collection of similar types of entities. Entities are represented by means of their properties, called Attribute or Column. All attributes have values which are the qualities or data about Entities that is to be stored. An Attribute describes a property or characteristic of an entity. Continuing with the above example, a student entity may have name, class, and age as attributes. Attribute is the smallest storage unit of any database.
π Definition β Entity: A name of a place, person, or thing about which something can be stored in a system; a real-world object that can be easily identifiable. π Definition β Attribute: A property or characteristic of an entity; the smallest storage unit of any database, having values that are qualities or data about entities. π Example: In a school database, Student is an entity, and its attributes include Name, Class, and Age.
AAAA. Relationships
Relationship represents how data is connected among entities in a given system. The association among the entities can also be termed as relationships. In our school example, the two entities, e.g., student and course, have an association or relation with each other as student enrolls in a course. Interaction among entities is captured using relationships.
BBBB. Process to Create ERD
The first step in creating the ERD is to define or determine the entities from the data. The second step is to chalk out the relationships and interaction between the entities identified in the very first step. The third step is to identify the cardinalities which is also known as degree of relationship, which is the number of entities in an entity-set which are associated (or linked) to the number of entities in another entity-set. There are three degrees of relationship, known as: One-to-One, One-to-Many, and Many-to-Many.
π‘ Why this matters: Understanding how to create an ERD and identify cardinalities is critical for designing a database that accurately reflects real-world business rules and data dependencies.
π Definition β Cardinality (Degree of Relationship): The number of entities in one entity-set that are associated with the number of entities in another entity-set. π Formula: Degrees of Relationship: One-to-One (1:1), One-to-Many (1:N), Many-to-Many (M:N) π Example: In a school, one student can enroll in many courses (One-to-Many relationship between Student and Course).
β Key Takeaways
An ERD is a vital graphical tool for visualizing how data is related in a database, created as the first step in the design process. Its three core componentsβentities (real-world objects like students), attributes (properties like name), and relationships (connections like enrollment)βmust be clearly understood. The process of building an ERD involves sequentially identifying entities, defining their relationships, and then specifying cardinalities (One-to-One, One-to-Many, or Many-to-Many). An attribute is the smallest storage unit in a database, and an entity set is a collection of similar entities. Mastery of these concepts is fundamental for translating business requirements into a solid database structure.
π§ Quick Revision Questions
- What are the three main components of an Entity Relationship Diagram (ERD)?
- Define an entity and provide an example from a school database.
- What is an attribute, and why is it considered the smallest storage unit in a database?
- List the three degrees of relationship (cardinalities) used in ERDs.
- Describe the three-step process for creating an ERD.
π Lecture 9 β Entities, Attributes and Relationship
π Overview: This lecture covers the fundamental concepts of database modeling, focusing on how to extract entities and attributes from user requirements. It explains the Entity Relationship Diagram (ERD) model and provides detailed explanations of different types of keys (primary, super, candidate, foreign, composite, surrogate) and their practical identification. The lecture also explores relationships between entities, their types, cardinality, optionality, and critical integrity constraints like referential integrity and cascading.
ποΈ Topics Covered
This module covers extracting entities from requirements specifications using a building apartment rental scenario, identifying attributes for each entity, defining primary keys and super keys, understanding unique keys and candidate keys, implementing foreign keys and composite primary keys, examining relationships between entities with cardinality and optionality, exploring the three basic relationship types (one-to-many, one-to-one, many-to-many), understanding referential integrity constraints and cascading mechanisms (cascade delete, cascade update, restrict), and applying surrogate keys as artificial identifiers.
π Lecture Summary
A. Extracting Entities from Requirements
In database design, designers first produce requirements specifications from users, then transform them into a formal representation schema. The Entity Relationship Diagram (ERD) is a graphical way of displaying entity, relationship, and attributes types. It is considered a very natural and easy-to-understand way of conceptualizing the structure of a database.
Consider the building apartment renting scenario: There are apartments, buildings, and customers. There are multiple floors in the building and on each floor there are multiple apartments; floor can have zero or no apartment. Each apartment can be rented to at most one customer but customer can rent out multiple apartments from same building. At the end of month a receipt is generated against which a rent is deposited.
The following entities are derived: BUILDING, APARTMENT, CUSTOMER, FLOOR, RECEIPT.
C. Extracting Attributes from Scenario
An attribute describes the facts, details, or characteristics of an entity. From the scenario:
- Building: Building Name (bname), Address, Phone Number
- Apartment: Apartment ID (aid), Covered Area, Status (available/rented), Rent
- Customer: Customer ID (cid), Name, Contact, CNIC
- Floor: Floor Number, Number of Apartments
- Receipt: Receipt ID (rid), Apartment Number, Date of Receipt, Amount
π‘ Why this matters: The ability to find meaningful names comes with fundamental understanding of what the model represents. Use meaningful business names wherever possible.
D. Primary Key
The primary key of a relational table uniquely identifies each record or row in the table. It is the primary key of reference for the table and is used throughout the database to help establish relationships with other tables. It usually comprises a single table column but may consist of multiple columns. Only one primary key can be defined per table.
π Definition β Primary Key: A column (or set of columns) that uniquely identifies each row in a table. Each row must have a distinct value. Primary key cannot contain a NULL value.
E. Identifying Primary Key from Sample Requirements
From the scenario:
- Building: Primary Key = Building Name (bname)
- Apartment: Primary Key = Apartment ID (aid)
- Customer: Primary Key = Customer ID (cid)
- Floor: Primary Key = Floor Number
- Receipt: Primary Key = Receipt ID (rid)
As a rule, a primary key should be minimal and should not contain unnecessary information. Building name fits this criteria as rest of the attributes can be complex containing unnecessary information.
F. Super Key
A superkey is a set of attributes within a table whose values can be used to uniquely identify a tuple or row in a database. A super key can be single or combination of multiple columns/attributes. There can be multiple superkeys in a table but no two rows will share the same combination or pair of super key.
π Definition β Super Key: A set of columns in a table for which there are no two rows that will share the same combination of values. A minimal superkey is the minimum number of columns that can be used to uniquely identify a single row.
G. Identifying Super Key from Sample Requirement
From the building scenario:
- Building (bname, address, phone#)
- Super Key: {(bname, address), (bname, phone)}
- Minimal Super Key: bname
H. Unique Key
A unique key identifies a record or row within a table. One or more unique keys may be declared for each data entity. Each unique key is composed from one or more data attributes. From the set of unique keys, a single unique key is selected and declared the primary key.
π Definition β Unique Key: Unlike primary keys, unique keys can have null values. Super keys and primary key can be declared as types of unique keys.
I. Candidate Key
A candidate key is the minimal number of attributes whose value(s) uniquely identify each entity occurrence. The set of unique keys determined for a data entity is often referred to as the candidate keys for that data entity.
π Definition β Candidate Key: A database table may have many candidate keys but at most one candidate key may be distinguished as the primary key. The candidate key is also termed as a potential primary key.
J. Identifying Candidate Key from Sample Requirements
From the scenario (CK = Candidate Key & UK = Unique Key):
- Building: NTN# = CK, UK
- Apartment: Apartment ID (aid) = CK, UK
- Customer: CNIC (UK), Email (UK)
K. Foreign Key
The foreign key is the field or attribute (or collection of fields) that logically relates two tables via common fields. In other words, foreign key is used to create relationship between two tables. The primary key of the first table or parent table becomes the foreign key of the child table.
π Definition β Foreign Key: When an attribute appears in more than one relation, its appearance usually represents a relationship between tuples of the two relations. Foreign key is not the primary key by default. Just like unique keys, foreign keys can have NULL values.
L. Example of Foreign Key
In the receipt database, each receipt is associated with a particular customer. Customer details are kept in a separate table; each customer is given a 'customerID' to identify it. Each receipt record has an attribute containing the customerID (cid) for that receipt. Then, the 'customerID' is the primary key in the customer table and that primary key will be the foreign key in the receipt table.
M. Composite Primary Key (CPK)
Composite primary keys are the primary key which consists of more than one attribute. CPK combines more than one attribute of an entity to make a unique value. Sometimes it becomes necessary to combine more than one attribute in order to uniquely identify the records.
π Definition β CPK: There will be just one primary key for each table. In relation between two entities, foreign key can become a part of CPK. CPK is unique as group and all the CPK values cannot be repeated. There are no composite foreign keys.
N. Example of Composite Primary Key (CPK)
In the building & apartments renting scenario, a CPK can be created by joining receipt ID and apartment number together to create a composite key which can uniquely identify each record.
O. Relationships
Relationships define how data is connected among entities in a given system. They are said to be a combination of cardinality and optionality where optional relationship is one in which there may or may not be a matching record in parent/child table and cardinality represents the concept of "how many" and normally it is 0 or more.
π Formula: R(elationship) = C(ardinality) + O(ptionality)
Relationships are bi-directional in nature. Relationship between two entities A and B is: (i) Relationship from A to B, (ii) Relationship from B to A.
P. Optionality with Example
Participation in entity relationship is either optional or mandatory. This means that one entity occurrence may or may not require a corresponding entity occurrence in a particular relationship.
π Definition β Optionality: A dotted line shows optional relationship (zero or one). A solid line shows mandatory relationship (exactly one).
Q. Cardinality with Example
Cardinality expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity. It tells what kind of relationship an entity has with another but does not state whether it's mandatory or optional.
π Definition β Cardinality: Tables can be related as "one-to-many" or "many-to-many." The symbol < or > means "more than one." Cardinality is read with opposite entity.
R. Basic Relationships
There are three kinds of relationships: One-to-Many, One-to-One, and Many-to-Many.
a. One-To-Many Relationship: A relationship between two entities A and B in which element of A may be linked to many elements of B, but a member of B is linked to only one element of A. This is the most common type of relationship.
b. One-To-One Relationship: A relationship between two entities A and B in which one element of A may only be linked to one element of B, and vice versa. There is no parent-child relationship in one-to-one relation scenario.
c. Many-To-Many Relationship: A relationship between two entities A and B in which A may contain a parent record for which there are many children in B and vice versa. Many-to-Many relations are not supported directly in the relational environment. To represent this kind of relationships, a third entity or intersection table is created where PK of two tables act as FK and CPK in third table.
S. Referential Integrity Constraint
Referential integrity is a relational database concept in which multiple tables share a relationship based on the data stored in the tables, and that relationship must remain consistent. When a foreign key value is used, it must reference a valid and existing primary key in the parent table otherwise the referential integrity will break.
π Definition β Referential Integrity: The child table can only be created if parent record is present as the child table is dependent on parent table.
T. Cascading
Cascading is the mechanism to ensure the foreign keys have valid reference in the parent table. Cascading defines the behavior of foreign key when the record from the parent table is deleted or updated.
V. Cascade Delete: If a row in the referenced table is deleted, then all rows in the referencing table with a foreign key value equal to the primary key value of the row should also be deleted.
W. Cascade Update: If the primary key value of a row in a referenced table is updated, all rows in the referencing table with a foreign key value equal to the primary key value of this row should also be updated to the new value.
X. Restrict Delete and Update: The parent record can neither be deleted nor updated if the child record exists. There is no restriction in deleting or updating the child records.
Y. Surrogate Keys
A surrogate key is an artificial key which is not a part of the system. The purpose of the surrogate keys is to avoid the complexity of the composite primary keys. Whenever the primary key is considered to be unsuitable, designers use surrogate keys which are artificially produced (most often system generated) and generally in numeric values.
Z. Example of Surrogate Keys
In the building-apartment rental scenario, the receipt ID can serve as an example of Surrogate Key. The receipt ID can be added automatically in a sequence. Instead of using a combination of one or more attributes of RECEIPT entity as a primary key, it would be more appropriate to use a sequential and auto generated ID as a primary key.
β Key Takeaways
The most critical concepts to remember from this lecture are the different types of keys and their specific rules: primary keys uniquely identify rows and cannot contain NULL, super keys are superset combinations of keys where minimal superkey is the smallest set, candidate keys are potential primary keys, foreign keys logically relate two tables, composite primary keys combine multiple attributes for uniqueness, and surrogate keys are artificial system-generated identifiers. Relationships are defined by cardinality (one/many) and optionality (optional/mandatory) and are bi-directional. The three basic relationship types are one-to-one, one-to-many (most common), and many-to-many (requires intersection table). Referential integrity ensures foreign keys reference valid primary keys, and cascading mechanisms (cascade delete/update and restrict) maintain data integrity during parent record modifications.
π§ Quick Revision Questions
- What is the difference between a super key and a minimal super key? Provide an example using the building entity.
- What are the key differences between a primary key and a unique key, specifically regarding null values and number allowed per table?
- How would you implement a many-to-many relationship in a relational database, and why can't it be directly supported?
- What happens to child records when a parent record is deleted under CASCADE DELETE versus RESTRICT DELETE?
- When would a designer choose to use a surrogate key instead of a composite primary key?