CS312 — Midterm Summary (Lectures 1–22)

📘 Lecture 1 — Module-01: Introduction to Data & Information

📖 Overview: This foundational lecture establishes the critical distinction between data and information, demonstrating how raw facts are transformed into decision-useful content. It introduces the concept of a database as an organized repository of data and explains why a Database Management System (DBMS) is essential for managing, retrieving, and controlling that data efficiently, contrasting this with manual data handling.

🗂️ Topics Covered

This lecture begins by defining data as raw material and information as processed, context-relevant data, using concrete examples like a telephone directory and student records. It contrasts life with and without databases, highlighting benefits such as reduced redundancy and easier access. The lecture explains why we have databases (not "information bases"), distinguishes a database from a DBMS, and finally breaks down the five core components of a DBMS: data, users, hardware, software, and procedures.

📝 Lecture Summary

A. Definition of Data

Data can be referred to as raw material from which we can draw conclusions after analysis, or the facts from which we can infer new facts. Data has nothing to do with decision making, but it lays down the foundation for decision making. Data is collected and further analyzed to convert it into information suitable for decision making. Data and information are two separate things and are not to be confused with each other.

🔑 Definition — Data: Raw material or facts from which we can draw conclusions after analysis or infer new facts. It has nothing to do with decision making but provides the foundation for it.

B. Examples of Data

The following are examples of data:

• Telephone Directory: a huge collection of data, with an internal structure of representation i.e., name, telephone number, city or address. Facts with structures are also termed as data.
• Student Record on Computer: a file on a computer containing data of ten thousand students is also data. This data too has an internal structure in the form of Student ID, Student Name, CGPA, and the like.

C. Definition of Information

Information can be defined as processed or organized data presented in a given context so as to make it useful related to the problem in hand. When a specific set of data is analyzed or interpreted, it becomes information that is more suitable for decision making. Simply stating, data becomes information when it becomes relevant to the decision problem.

🔑 Definition — Information: Processed or organized data presented in a given context to make it useful for the problem at hand. Data becomes information when it becomes relevant to the decision problem.

💡 Why this matters: This distinction is the core of why we build databases—to store raw data efficiently so we can later retrieve and process it into information for a specific purpose.

D. Examples of Information

The following are examples of information:

• Telephone Directory: as stated above, the telephone directory is a huge set of data, and when we process this set of data to get the telephone number of a specific dentist or a colleague, it becomes information.
• Student Record on Computer: a file on a computer containing data of ten thousand students is again a huge set of data, which converts into information when processed to get a list of students with a CGPA greater than three.

E. Comparison of Data & Information

Data and information are often considered to be synonyms of each other, but the truth is that there is nothing in common between them. Both have different meanings and characteristics. Information is a subset of data, typically the required set of data. Data leads to information, so information is considered to be dependent on data.

F. Example to Elaborate the Difference Between Data and Information

Consider a file storing flight routes from one city to another. Data is structured in the form of Flight ID, Origin, Destination, Date of Departure, Time of Departure, etc. The file contains two thousand routes. This file qualifies to be called data because it's a huge set of structured data. This huge data turns into information when processed or filtered to get details about a flight for a specific destination, for example, from Lahore to Karachi on April 15th.

📌 Example: A flight schedule file with 2,000 records is data. Filtering it to find flights "from Lahore to Karachi on April 15th" produces information.

G. Life With & Without Database

A database is organized data with digital procedures, which is able to handle different sets of data with a very large capacity. A database is a huge repository of data that comes with ease to manage. Therefore, databases have become a substitute for the manual handling of lots of files, which come with very high risks and chances of data loss. Access to data has become easy and less time-consuming due to databases.

H. Benefits of Using Databases

As mentioned above, the advantages of using databases are enormous. Benefits of using databases vary from reducing redundancy in data to getting rid of unnecessary sets of data. Without databases, loads of data are handled manually with many potential risks and are difficult to access. With databases, it's easy to access large sets of data when required. The example of a Driving License and Passport serves as a good example in which the profile data is the same but it is repeated over again, highlighting the problem of redundancy that databases solve.

I. Why Database, Not Information Base?

Databases deal with the storage or organization of data, and the main focus of databases is to optimize data storage. On the other hand, information – as it is a subset of data – is all about the retrieval of specific data for different purposes. As information is dependent on data, databases are the basic requirements for information, which can then be used for multiple purposes.

💡 Why this matters: The term "database" focuses on the storage mechanism. If we called it an "information base," we would miss the crucial first step of efficiently storing and organizing the raw data before it can be turned into information.

J. Difference Between Database and Database Management System (DBMS)

A database is all about the design and structure of the data, and a DBMS works as a tool to manipulate or analyze the data inside the database. A DBMS is a whole system to manage a digital database, its storage, creation, and retrieval (of information). Differentiating between these two terms: a database is a huge set of data, while a DBMS is a tool to manage or control that data in a database, with the major function of data retrieval.

🔑 Definition — Database vs. DBMS:

• Database: The design and structure of the data (e.g., Employee Records Columns).
• DBMS (Database Management System): A tool/system to manage the digital database, its storage, creation, and retrieval (e.g., Oracle).

K. Example of Database and DBMS

• Example 01:
  • Database: Employee Records Column with Employee ID, Name, Qualification, Date of Joining, etc.
  • DBMS: Oracle to create and load employee records.
• Example 02:
  • Database: Individual files on Hard Disk.
  • DBMS: Operating System to read the file from Hard Disk.

L. Components of Database Management System

A DBMS is the aggregate of the following components:

1. Data: The data inside the database which is to be processed, organized, retrieved, or updated.
2. User: The people who manage the databases and perform different operations on them. They can be end users, database administrators, or application programmers.
3. Hardware: A set of physical electronic devices such as computers (together with associated I/O devices like disk drives), storage devices, and I/O channels that make the interface between computers and the real world systems.
4. Software: The set of programs used to handle the database and to control and manage the overall computerized database.
5. Procedures: Instructions and rules that help to design the database and to use the DBMS.

⭐ Key Takeaways

The single most critical understanding from this lecture is the clear, operational distinction between data (raw, unprocessed facts) and information (processed data relevant to a decision). A database is a structured repository for the former, while a DBMS is the system of software, hardware, users, data, and procedures used to manage it. The primary goal of a database is to optimize data storage, making it a prerequisite for extracting information. Finally, the core components of a DBMS are the five interdependent parts: data, users, hardware, software, and procedures.

🧠 Quick Revision Questions

1. What is the fundamental difference between data and information in the context of decision making?
2. If you have a spreadsheet of all student names and their final exam scores, is it data or information? What would make it information?
3. Why is it called a "Database" and not an "Information Base"?
4. What is the difference between a database and a Database Management System (DBMS)? Give an example of each.
5. List the five core components of a DBMS and briefly describe the role of one of them.

📘 Lecture 2 — Data Storage Mechanism

📖 Overview: This lecture explains the foundational data models used in database design, starting from the basic architectural unit of a data model through various historical models. It covers flat file, hierarchical, and network database models, detailing how each stores, organizes, and retrieves data, and why understanding these models is crucial for designing efficient modern databases.

🗂️ Topics Covered

The lecture begins with the definition and importance of a data model as the architectural unit of a database. It then explains the flat file database model, including how it works with columns, rows, and duplication issues. Next, the hierarchical database model is introduced as a tree-like structure based on one-to-many parent-child relationships. Finally, the network database model is covered as a flexible alternative that allows multiple parents and many-to-many relationships.

📝 Lecture Summary

A. Data Model Basics

A data model is the basic architectural unit of a database, determining the structure, organization, and storage of data. It defines the connections, processing, and storage of data in a system. The data model organizes data and determines how data elements relate to each other. It acts as the map or design of the database and is difficult to change once data is inserted.

M. Flat File Basics

A flat file is a database stored on its host computer system as an ordinary file, requiring the complete file to be read into memory for processing or performing operations. This database contains one record per line and prefers plain text (word or notepad) or ASCII File format. The flat file database design puts all data around a single table, and data has no interrelationships.

N. How Flat File Works

Data in a flat file is stored in columns and rows, with each row separated by a comma or tab. Each line of text holds one record and contains many fields, often with duplicate data. There is no relation between data, and as more records are added, the chances of data duplication increase. This redundancy results in reduced integrity of data, making it more unreliable. Additionally, duplication makes it difficult to update records because there are non-unique records in the database.

📌 Example: In a flat file storing customer orders, each line might contain "John,New York,Order1,Order2" where John's name and city are repeated for every order, creating duplicate data.

O. Hierarchical Database Basics

The hierarchical database is one of the two main data models proposed by IBM's IMS System. It is a data model in which data is stored in a tree-like structure. Data is stored as records (combination of rows and columns), and these records are connected through links. The Hierarchical Data Model organizes a database with multiple one-to-many relationships. The structure is based on the rule that one parent can have many children, but children are allowed only one parent. To retrieve data from a hierarchical database, the whole tree must be traversed starting from the root node.

🔑 Definition — Root Node: The topmost node in a hierarchical database tree from which traversal begins to retrieve data.

📐 Formula: One-to-many relationship → One parent record can link to many child records, but each child has exactly one parent.

📌 Example: An organizational chart where one manager (parent) oversees multiple employees (children), but each employee reports to only one manager.

P. How Hierarchical Database Works

In the hierarchical data model, data is retrieved using traversal — meaning visiting the root node and navigating through its children. There is a one-to-many relationship between root and its child nodes. Each parent record stores the address of its child records, and every child record acts as a parent record for its next level.

💡 Why this matters: This structure makes hierarchical databases fast for accessing data from a known root but inefficient for finding data that requires starting from a middle or leaf node.

Q. Network Database Basics

Opposite to the hierarchical model, the network model allows each record to have multiple parents and child records. This structure forms a generalized graph structure, and this model can be seen as an upside-down tree. This model is considered a very flexible way of representing objects and their relationships with each other. The network database model is easy to navigate because of the linkages between the information and many-to-many relationships.

🔑 Definition — Many-to-many relationships: A relationship where multiple records can be linked to multiple other records, unlike the one-to-many restriction of hierarchical models.

📌 Example: In a university database, a student can enroll in multiple courses, and each course can have multiple students — a many-to-many relationship.

R. How Network Database Works

There is no concept of parent-child relationship in this type of data model. Each record stores the addresses of all the referencing records. As it supports many-to-many relationships between root and child nodes, any record can be linked to any record, and thus there is no hierarchy in this type of data model.

📌 Example: A database of books and authors where one book can have multiple authors, and each author can write multiple books — each record stores pointers to all related records.

⭐ Key Takeaways

The data model is the fundamental architectural unit determining database structure and storage, and it is difficult to change after data insertion. Flat files store data in a single unstructured table with no relationships, leading to data duplication, reduced integrity, and update difficulties. Hierarchical databases use a tree structure with one-to-many relationships where each child has one parent, requiring traversal from the root for data retrieval. Network databases overcome hierarchical limitations by allowing many-to-many relationships where any record can connect to any other record, creating a flexible graph structure. The evolution from flat files to network models shows increasing flexibility in representing data relationships, which is essential knowledge for understanding relational and modern database designs.

🧠 Quick Revision Questions

1. What is a data model and why is it difficult to change after data is inserted?
2. How does data duplication in flat files affect data integrity and update operations?
3. What is the key rule about parent-child relationships in a hierarchical database model?
4. How does the network database model differ from the hierarchical model in terms of relationship types?
5. In the network model, why is there no concept of parent-child relationship?

📘 Lecture 3 — Three-Tier Architecture

📖 Overview: This lecture introduces the Three-Tier Architecture as a solution for managing application complexity and enterprise scalability. It explains how dividing an application into three independent tiers—Presentation, Business Logic, and Data—enables modular development, fault tolerance, and easier maintenance. The lecture also covers roles in DBMS, client-server architecture, and distributed processing.

🗂️ Topics Covered

The lecture defines tiers as logically related but physically independent components, introduces the rationale for 3-Tier architecture, and explains each of the three tiers: External Level (Client Interface), Conceptual Level (Business Logic), and Internal Level (Data Tier). It emphasizes layer independence with illustrative examples, then moves to roles in database management (Application Programmer, End User, DBA, System Analyst), client-server architecture with a chat example, and finally distributed processing.

📝 Lecture Summary

A. Introduction to 3-Tier Architecture

Tiers are defined as the collection of logically related but physically independent components of an application. In this architecture, the application is divided into 3 tiers, where each tier has its own boundary. Each tier contributes to the whole, and several tiers join to make something complete. By segregating an application into various tiers, developers acquire the option of modifying or adding a specific layer, instead of reworking the entire application.

🔑 Definition — Tier: a collection of logically related but physically independent components of an application

S. Rationale for 3-Tier Architecture

3-Tier architecture emerged as a solution to application complexity and enterprise scalability. This new architecture proposes three layers, allowing different teams to work on different layers simultaneously, thus making the development process easier. Dividing an application into 3-Tiers helps avoid a single point of failure—failure of one tier does not cause the whole application to crash.

T. External Level (Client Interface)

This is the top-most layer of 3-Tier architecture, also known as the Presentation Layer. This tier manages the input/output data and their display. In other words, it displays what clients view. This tier is responsible for formatting the input/output data into a presentable form. It can communicate one tier below with the application tier to display information. In simple words, it is a tier that users can access directly, such as mobile applications, browsers, and forms.

U. Conceptual Level (Business Logic)

This tier—also referred to as the application tier—is the middle tier that bridges the gap between the user interface and the underlying database. This tier consists of business and data rules. In this tier, the logic of the business is encapsulated (e.g., Programming, Class Diagram, ERD). This tier receives requests from the presentation tier only, interprets or processes the requests according to the defined rules, and sends back the results to the presentation tier only. It can receive requests and send responses to the presentation tier only, and it can send requests to only the below tier, i.e., Internal Level (Data Tier).

V. Internal Level (Data Tier)

This tier is responsible for storing information and data needed for the system and for optimizing data access. Data needed by the application logic layer are retrieved from the database, and the computation results produced by the application logic layer are stored back in the database. This tier can receive requests from the Conceptual Level (logic tier) only and can send responses to that tier only. All decisions related to data storage are kept here, and these storage mechanisms are independent of other tiers.

W. Layer Independence

The tiers mentioned above have no interdependence and work exclusively. Every tier is totally unaware of the internal working or the logics of other tiers, and the decisions made by the tiers are also independent. In simple words:

• Data layer is not aware of the internal working of the logic layer
• Logic layer is not concerned about the workings of the data layer
• Presentation layer works in total independence from other layers

💡 Why this matters: This independence means changes can be made to one tier (e.g., changing the database) without affecting the other tiers, making the system highly flexible and maintainable.

X. Example – Presentation Layer Independence

Consider the following situation: Query: Get Total Sales. A manager of a company needs to run a sales query to find out the list of all sales made last year. He will use the presentation tier (client interface) to send this request to the application tier. Suppose this is done using a form, browser, or mobile app. The way the request is made cannot affect the way the data is retrieved. All other tiers will work in total independence. Similarly, all other tiers cannot affect the way data is presented in the presentation tier.

Y. Example – Logical Layer Independence

Continuing with the same example: Query: Get List of all Sales made last year. The structure or the workings of this tier are also exclusive to the logical tier. Using if-then, while, or any other structure (i.e., how the query is made) is internal to this tier only and cannot affect the way data is displayed.

Z. Example – Data Layer Independence

As shown in the figure, the application tier will send the query to the data tier, and it will respond to the application tier only with the requested results. What logic the application has used or will use is not the concern of the data tier; it will just provide the required results. The application tier will perform the activity as it is programmed to do and will forward the results to the presentation layer, which will display the results to the end user. None of the mentioned tiers can affect the working or processes of the other tiers.

AA. Roles in Database Management System (DBMS)

There are the following roles in a database environment:

• Application Programmer: develops the application that provides the required functionality for the end user.
• End User: the 'clients' for the database, which has been designed, implemented, and maintained to serve their information needs.
• Database Administrator (DBA): responsible for the physical realization of the database, including physical database design and implementation, security and integrity control, maintenance of the operational system, and ensuring satisfactory performance of the applications for users.
• System Analyst: a person who uses analysis and design techniques to solve business problems using information technology.

BB. Client Server Architecture

Client-server architecture is an architecture of a computer network in which many clients (remote processors) request and receive service from a centralized server (host computer). Client computers provide an interface to allow a computer user to request services of the server and to display the results the server returns. Servers wait for requests to arrive from clients and then respond to them. The server is powerful in terms of processing (e.g., file server, printer server, email server). The client is connected to the server via a communication link. The client interacts with the server when it requires access to any additional functionality that does not exist in its own system.

CC. Example – Client Server Architecture

The client-server architecture is best illustrated with the example of chatting over the internet, where communication takes place between multiple clients with multiple clients. The chat clients send the user-typed message to the server; the server then aggregates typing from all the clients and forwards it to all the clients. User's clients on the other side display the aggregated typing from the chat server.

DD. Distributed Processing

A distributed system is a combination of multiple components located on a network, and these components communicate and coordinate their actions by passing messages. These components interact with each other to achieve a common goal. The processing is divided into multiple systems, increasing the processing power. In distributed computing, a problem is divided into many tasks, each of which is solved by one or more computers, which communicate with each other by message passing.

EE. Example of Distributed Processing

(To be done by Sheraz SB)

⭐ Key Takeaways

The most critical concepts from this lecture are: (1) The Three-Tier Architecture separates an application into Presentation, Business Logic, and Data tiers, providing modularity and fault tolerance. (2) Layer independence means each tier is unaware of the internal workings of other tiers, allowing changes to one tier without affecting others. (3) The database environment involves four key roles: Application Programmer, End User, Database Administrator, and System Analyst. (4) Client-server architecture involves clients requesting services from a centralized server, as illustrated by the chat example. (5) Distributed processing divides a problem into tasks solved by multiple computers communicating via message passing.

🧠 Quick Revision Questions

1. What are the three tiers in the Three-Tier Architecture, and what is the primary responsibility of each?
2. Explain why layer independence is important for application maintenance and scalability.
3. Describe the four roles in a DBMS environment and their key responsibilities.
4. Using the chat example, explain how client-server architecture works in terms of request and response.
5. How does distributed processing differ from client-server architecture in terms of task execution and communication?

📘 Lecture 4 — Fact Finding Technique

📖 Overview: This lecture introduces the essential fact-finding techniques used in database development to gather accurate requirements. It covers multiple methods including document examination, interviews, observation, research, and questionnaires, explaining how each technique contributes to understanding the current system and identifying data needs for the new database.

🗂️ Topics Covered

The lecture covers six main fact-finding techniques: examining documents basics and the importance of documenting existing systems, conducting interviews with their types (unstructured and structured with open-ended and close-ended questions), observing enterprise operations with best practices, researching existing solutions, and using questionnaires with free-format and fixed-format questions. Each technique includes practical examples and guidelines for effective implementation.

📝 Lecture Summary

A. Examine Document Basics

Examine documents is the first step in database development that reveals how the need for a database arose and what symptoms and problems in the current system led to this decision. This technique is critical for studying the current system and identifying the affected part of the organization. It is also valuable for identifying the data to be collected and reported by the system.

🔑 Definition — Examine documents technique: A fact-finding technique where existing documents are reviewed to understand how the need for a database arose and to study the current system.

GG. Documenting the Examine Document

Documenting the existing systems leads to identifying the type of data coming in and out of the system, laying the foundation for database requirements. This documentation identifies the data being handled and the relationship between the data, explaining how data flows from one part to another, thus creating a better and more efficient system. The lecture illustrates this with a figure showing data about customers and services requested, where problems are categorized into two categories, demonstrating how examining documents reveals information flows among different stakeholders.

HH. Conducting Interviews Basics

Interviewing is the most commonly used and normally most useful technique for fact finding, where the business analyst gathers information through face-to-face interaction. The objectives of interviews include fact finding or verifying facts, getting more diverse ideas and information, gathering requirements, involving the end user to generate enthusiasm, and making them feel engaged in the whole project.

🔑 Definition — Interviewing: A fact-finding technique where the business analyst gathers information by having face-to-face interaction with stakeholders.

II. Types of Interview

There are two types of interviews:

• Unstructured Interviews: These interviews are conducted with a general goal in mind with very few specific questions. The interviewer lets the interviewee drive the discussion and provide direction to the interview.
• Structured Interviews: In this type, the interviewer has a specific set of questions which can be further divided into two categories:
  • Open-ended Questions: These let the interviewee respond in any way that seems appropriate with no specific options to choose.
  • Close-ended Questions: These restrict the answers to either a specific choice or very short answers.

JJ. Example of Type of Interviews

📌 Example 01 — Unstructured Interview: A researcher asks a caregiver "What kinds of illnesses do children in this village get?" The caregiver freely responds about diarrhea, cough, cold, measles, fever, typhoid. The researcher then follows with "Are there any other during cold and hot weather?" and the caregiver adds pneumonia and whooping cough. The conversation continues naturally about measles symptoms and transmission.

📌 Example 02 — Structured Interview: The interviewer asks specific questions like "Could you tell me the tasks you do to prepare salaries per month?" and "How do you know person monthly salary?"

KK. Observing the Enterprise in Operations

Observation is one of the most effective fact-finding techniques as it gives the analyst a chance to participate in or watch the person perform the activity, resulting in deep understanding of the system. This technique is particularly useful when the validity of data collected through other methods is a serious concern or when the system is too complex for the end user to explain properly. To ensure success, it is necessary to know as much about the individual and activity as possible, including when low, normal, and peak periods occur for the activity being observed.

🔑 Definition — Observation: A fact-finding technique where the analyst participates in or watches a person perform an activity to gain deep understanding of the system.

LL. Have and Have Not's for Observing

The observation should be recorded immediately or in parallel with the observation. The observer must not wait until the observation ends to write the complete process; rather, notes should be taken in bullet form during the observation to avoid losing any point. The observer must not interrupt or ask too many questions from the end user while they are performing the activity, as this would temper the activity and prevent the observer from getting real information.

MM. Research

Research is valuable for solving the problem in hand. Internet, journals, and reference books are good sources of information about how others have solved related problems or what solutions are available. Research can save a lot of time as ready-to-deploy solutions can be found to fulfill requirements. It is also helpful in validating information gathered through observation, as there are chances that the actual execution of the activity differs from what was observed.

NN. Questionnaire

Questionnaires are specific purpose documents used to gather information from a large number of people while maintaining some control over the responses. There are two types of questions:

• Free-Format Questions: These offer greater freedom to respondents as they answer in the space provided after the question. Responses are more subjective in nature and difficult to tabulate.
• Fixed-Format Questions: These require specific responses where individuals choose from available options. Responses are more specific and easier to tabulate, but the disadvantage is that the researcher doesn't get additional information that might be worth knowing.

🔑 Definition — Questionnaire: A specific purpose document used to gather information from a large number of people while maintaining some control over the responses.

OO. Example of Questionnaire

📌 Example: Given the requirement "What are current trends among students in terms of interest in media programs?", the lecture shows a sample questionnaire in Figure 1. The questionnaire includes a title "Student Media Interest Survey" and contains questions about age, gender, preferred media programs, time spent, and reasons for interest, using both free-format and fixed-format questions.

⭐ Key Takeaways

The lecture presents six essential fact-finding techniques for database development: examining documents, conducting interviews (both unstructured and structured with open-ended and close-ended questions), observing enterprise operations, researching existing solutions, and using questionnaires. Each technique has specific advantages and best practices — interviews are most commonly used, observation provides deep understanding but requires immediate note-taking without interruption, and questionnaires are best for gathering information from large numbers of people. Document examination is the critical first step that reveals data flows and relationships within the organization. The choice of technique depends on the complexity of the system, the number of stakeholders involved, and the need for validation of collected data.

🧠 Quick Revision Questions

1. What are the six fact-finding techniques covered in this lecture?
2. What is the difference between unstructured and structured interviews?
3. Why should notes be taken during observation rather than after it ends?
4. What are the advantages and disadvantages of fixed-format questions in questionnaires?
5. How can research help in validating information gathered through observation?

📘 Lecture 5 — Implementing Fact Finding Techniques

📖 Overview: This lecture covers the critical transition from fact-finding activities to formal requirement writing in the database design cycle. It provides practical examples of translating interview and questionnaire responses into structured requirements, and explains how to derive a system definition from those requirements to guide the entire development process.

🗂️ Topics Covered

The lecture explains the process of converting fact-finding results into written requirements, using detailed examples from a rental system interview and a restaurant questionnaire. It then defines how to create a comprehensive system definition from these requirements, including concept definition and the System-of-Interest (SoI), followed by a step-by-step extraction process and a list of essential database features.

📝 Lecture Summary

A. From Fact-finding to Requirement Writing

Requirement writing is the immediate next step after fact-finding in the cycle of database design and development. The requirements defining the systems are to be converted into written format. Along with the system definition, it also contains and explains the data flow in the system and the relationships between the data. The requirement or the specification document describes in detail the data to be held in the database and how the data is to be used (i.e., the processes and the relationships). These requirements are specific and measurable in nature. The system requirement, like how fast a transaction must run or how many transactions must be processed in a specific interval of time, form the basis of a number of decisions in database design and development.

💡 Why this matters: Without precise, written requirements, the database design will be based on assumptions and may fail to meet stakeholder needs. Measurable requirements ensure the final system can be tested and verified.

PP. Example of Translating Interview to Requirements

Consider the following example in which the interviewer asks questions to get the requirements for a high-level database for a rental system of a certain plaza. Following the interview are the requirements gathered through the interview.

Interview:

1. Interviewer: How many building types are there for rent? Interviewee: Primarily Apartments, some Offices and few Shops
2. Interviewer: How many are total apartments, total offices and total shops? Interviewee: Ahhhh... yes 15 Apartments, 5 offices and 3 shops
3. Interviewer: How many floors are there in the plaza? Interviewee: 6 floors but we have apartments only on 3 floors and on remaining floors we have shops and offices
4. Interviewer: Do we have fix rent for each apartment or does it vary from Floor to Floor? Interviewee: Rent varies from Floor to Floor of apartment.
5. Interviewer: Did you send rent bill / receipt at the end of month? Interviewee: We usually send bill for two-months but receive installments on monthly basis also.
6. Interviewer: Can you tell me specific data you store about customer into the system? Interviewee: We usually ask for name, address and phone no.
7. Interviewer: Did you assign any number to customer? Interviewee: Yes, I miss out.
8. Interviewer: If multiple apartments / shops / offices are rented to same customer, then do we have same contract no and customer no? Interviewee: Good question, let me think....pause, we usually assign different contract no but one bill to such case.

Requirements (derived from interview): In a plaza renting system, there are three types of buildings primarily: apartment, offices and shops. There are multiple floors in a plaza and for 3 floors apartments are allocated but for remaining there can be a mix of shops and offices. When a customer is registered, usually customer id, name, phone and address is stored as profile and a bill is generated for the rent on bi-monthly basis but customer has an opportunity to pay the bill on monthly basis. Contract is signed and uploaded against every building type. Contract id is assigned to every contract. It is possible that a customer may rent multiple building types; in this case, a different contract no is assigned to same customer. One Bill is generated for customers with multiple building types.

📌 Example: From the interview, the requirement "Rent varies from Floor to Floor of apartment" is captured. The requirement "One Bill is generated for customers with multiple building types" translates the specific business rule discovered in question 8.

QQ. Example of Translating Questionnaire in Requirements

Following is a sample questionnaire to elaborate the translation of data gathered through questionnaires into requirements.

Questionnaire:

1. What is price (per person) range of food offered?
   • 500-1000
   • 700-1200 (checked)
   • 1000-1500
2. Is cost of dish calculated on the basis of?
   • Cost of ingredient only (checked)
   • Cost of ingredient and electricity also
   • Cost of ingredient plus % of profit
3. Is % of Profit?
   • 20% (checked)
   • 15%
   • 10%
   • None of the Above
4. Are ingredients and Quantity mentioned in?
   • Menu
   • Recipe (checked)
   • Restaurant Manual

Requirements (derived from questionnaire): In a restaurant management system, there are many dishes offered either as individual serving or buffet. Price range between 700-1200 is targeted which will include drinks and taxes also. Cost of every dish is calculated individually depending on ingredients involved only. Each ingredient is listed in recipe and quantity is also mentioned. Cost is calculated as per market rate of ingredient with 20% profit to cater for management and miscellaneous cost.

🔑 Definition — Questionnaire Translation: The process of converting structured, closed-ended questionnaire responses into clear, actionable system requirements by interpreting the checked options and implied business rules.

📌 Example: The checked option "Cost of ingredient only" translates to the requirement: "Cost of every dish is calculated individually depending on ingredients involved only." The checked option "20%" translates to: "Cost is calculated... with 20% profit."

RR. Defining System Definition from Requirements

System definition is defined by listing down the details of the whole system. These details, when properly concluded, form the basis of decisions regarding the scope and the boundaries, the architecture and the design of the system to be developed. Concept definition artifact lays down the foundation of system definition; concept definitions are activities in which the needs and the requirements of all the stakeholders are closely examined before defining the system. The concept definition is the articulation of System-of-Interest (SoI) – the model of system on the basis of needs and requirement – which is a collective set of all elements of any system being considered by the lifecycle.

🔑 Definition — System Definition: A document that details the scope, boundaries, architecture, and design of the system to be developed, derived from the requirements and stakeholder needs. 🔑 Definition — Concept Definition Artifact: The foundation activity that examines stakeholder needs and requirements before defining the system. 🔑 Definition — System-of-Interest (SoI): The model of the system created from the needs and requirements, representing the collective set of all elements of the system under consideration.

SS. Process of Extracting System Definition

System definition is based on the concept definition, a deep understanding of concepts, needs, expectations and stakeholders' requirements. System is further divided into subsystems through top-down decomposition of system-of-interest in order to reach the exact destination. This same model is shown in Figure 13 (see text). The needs and the concepts along with the stakeholder requirements are gathered to form a system-of-interest, and this is further divided into subsystems to fulfill the desired requirements.

The system should list all the important features for a database system and these should include the following:

• Initial database size
• Database rate of growth
• The types and average number of record searches
• Performance
• Security
• Backup and recovery
• Legal issues etc.

🔑 Definition — Top-Down Decomposition: The process of breaking a system-of-interest into smaller, more manageable subsystems to reach the specific design destination.

📐 Formula/Model: Needs + Concepts + Stakeholder Requirements → System-of-Interest (SoI) → Subsystems → Fulfilled Requirements.

⭐ Key Takeaways

The most critical lesson is that fact-finding is useless without proper translation into precise, written requirements. You must master converting open-ended interview answers and structured questionnaire responses into formal requirement statements, preserving all business rules and constraints like "one bill for multiple contracts" or "20% profit on ingredient cost." The system definition then synthesizes these requirements into a comprehensive blueprint that determines the scope, architecture, and design of the database, further decomposing into subsystems via top-down analysis. Essential database features like initial size, growth rate, performance, security, backup, and legal issues must be explicitly documented. For the exam, remember the direct line from fact-finding technique → requirement → system definition → database design.

🧠 Quick Revision Questions

1. What is the immediate next step after fact-finding in the database design cycle?
2. In the plaza rental system example, what business rule was discovered about billing when a customer rents multiple building types?
3. In the restaurant questionnaire example, what is the profit percentage applied, and on what cost basis?
4. What is a System-of-Interest (SoI), and how does it relate to concept definition?
5. List four of the seven essential features a database system definition must include.

📘 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

1. What are the four essential attributes of software requirements, and why is each important for database design?

2. How does requirements engineering act as a bridge between real-world needs and software capabilities?

3. What is the difference between requirement management and requirement development?

4. Define defect versus bug — provide an example of each from the lecture.

5. 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

1. 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?
2. What is the difference between a NULL value and zero or an empty string in a relational database?
3. How does Physical Data Independence (Rule 8) differ from Logical Data Independence (Rule 9)?
4. What are the four SQL subsets, and what specific commands does each include?
5. 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

1. What are the three main components of an Entity Relationship Diagram (ERD)?
2. Define an entity and provide an example from a school database.
3. What is an attribute, and why is it considered the smallest storage unit in a database?
4. List the three degrees of relationship (cardinalities) used in ERDs.
5. 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

1. What is the difference between a super key and a minimal super key? Provide an example using the building entity.
2. What are the key differences between a primary key and a unique key, specifically regarding null values and number allowed per table?
3. How would you implement a many-to-many relationship in a relational database, and why can't it be directly supported?
4. What happens to child records when a parent record is deleted under CASCADE DELETE versus RESTRICT DELETE?
5. When would a designer choose to use a surrogate key instead of a composite primary key?

📘 Lecture 10 — Extended Entity Relationship Diagram

📖 Overview: This lecture introduces the Extended Entity Relationship Diagram (EERD), which enhances the basic ER model by adding super types and sub types to represent complex database systems more accurately. It explains how common attributes are shared through inheritance to avoid redundancy, and covers the key properties of exhaustiveness and mutual exclusivity that govern super type-sub type relationships.

🗂️ Topics Covered

The lecture covers the definition and purpose of the Extended Entity Relationship Diagram (EERD), explains the concepts of super types and sub types with inheritance rules, provides examples of how common attributes are shared between super types and sub types, and discusses two critical properties: exhaustiveness (requiring at least two sub types with unique attributes) and mutual exclusivity (each super type instance belongs to only one sub type).

📝 Lecture Summary

A. Extended Entity Relationship Diagram (EERD)

The EERD is an extension of the original Entity Relationship Diagram (ERD) that incorporates the concept of reusability. It was developed to reflect more precisely the properties and constraints found in more complex systems. Common attributes that are repeated among multiple entities are placed in a Super Type (or Super Entity) and are shared with Sub Types to avoid redundancy. Attributes and relationships of super types are shared with sub types, but this relationship is not bottom-up — meaning a Super Type cannot access any attributes or relationships of its sub types. The EER model includes all concepts from the ER model and adds the concepts of Sub-Type and Super-Type. Extended ERDs are high-level conceptual models that accurately represent the requirements of complex databases.

AAAAA. Super Types

A Super Type (also called a super class) contains common attributes that are shared by multiple sub types. Many sub classes or sub types can be derived from a single super type. The details of the super types are available in the sub types, but super types do not know anything about sub types. In simple words, it is an entity type that has a relationship with one or more sub types.

🔑 Definition — Super Type: An entity type that has a relationship with one or more sub types, containing common attributes that are shared downward through inheritance.

BBBBB. Sub Types

A Sub Type (or sub class) is derived from the super class and can have its own attributes and relationships. Besides having its own relationships and attributes, a sub type inherits all the relationships and attributes of its super class. A class derived from another class is called a subclass (also a derived class), while the class from which it is derived is called a super class (also a base class or parent class).

🔑 Definition — Sub Type: A class derived from a super class that inherits all attributes and relationships of the super class while also having its own unique attributes and relationships.

CCCCC. Example of Super Type & Sub Type

The lecture presents a diagram where the Staff entity acts as the Super Type with common attributes: sno, sname, designation, address. Two Sub Types are derived: Full-time and Part-time. Instead of writing these four common attributes (sno, sname, designation, address) multiple times for each employee type, they are written only once in the Staff super type and are shared by the Full-time and Part-time sub types through inheritance.

📌 Example: Staff entity has attributes sno, sname, designation, address. Full-time and Part-time sub types inherit these attributes automatically, so they only need to define their own unique attributes.

DDDDD. Exhaustive

Exhaustive is the first property of Super & Sub Types. It states that there must be at least two sub types, and each sub type must have at least one unique attribute. A sub type with no specific attributes is not valid. In other words, every instance of the super type is also an instance of one of the sub types.

🔑 Definition — Exhaustive: A property requiring at least two sub types derived from a super type, with each sub type having at least one specific attribute of its own.

📌 Example: If we remove the Salary attribute from the Full-time sub type, it is no longer a valid sub type because according to the Exhaustive rule, there must be at least one specific attribute for the sub type to qualify as valid.

EEEEE. Example of Exhaustive

The lecture uses a diagram to illustrate the Exhaustive property. A Staff super type has two sub types: Full-time (with unique attribute salary) and Part-time (with unique attribute hourly_rate). Each sub type must have at least one unique attribute to be considered valid.

FFFFF. Mutually Exclusive

Mutually Exclusive is the second property of Super & Sub Types. Sub types are mutually exclusive because they do not know about each other. Attributes and relationships that a specific sub type has with other entities are not shareable with or known to other sub types in the system. Every sub type maintains isolation of relationships and attributes from other sub types. This rule articulates that every instance of the super type belongs to one and only one sub type.

🔑 Definition — Mutually Exclusive: A property where each instance of the super type belongs to exactly one sub type, and sub types cannot access each other's attributes or relationships.

📌 Example: If sno=1 is associated with the Full-time sub type at a particular time, it cannot simultaneously be associated with the Part-time sub type. The Part-time sub type is not aware of this association due to the Mutually Exclusive rule.

GGGGG. Example of Mutually Exclusive

A diagram shows a Staff super type with sno=1 that can be linked to either Full-time or Part-time, but not both at the same time. This enforces that each employee instance belongs exclusively to one employee type.

HHHHH. Example of EERD

The lecture presents a complete EERD example for a HR System:

• There are different types of employees: Full-time and Part-time.
• Only Full-time employees can avail the facility of a loan.
• All types of employees (both Full-time and Part-time) can apply for study leaves.
• The Account department prepares salary slips in the same way for all employees.

The example shows how the EERD models these business rules: common employee attributes are in the super type, while specific attributes and relationships (like loan access for Full-time employees) are placed in the appropriate sub types.

💡 Why this matters: This example demonstrates how EERD captures real-world business rules where different employee types have different privileges and relationships, while sharing common data through inheritance.

⭐ Key Takeaways

The EERD extends the basic ER model by introducing super types and sub types to reduce data redundancy through inheritance of common attributes. Super types contain shared attributes that are inherited downward by sub types, but super types cannot access sub type attributes. Two critical properties govern sub types: Exhaustive requires at least two sub types each with at least one unique attribute, and Mutually Exclusive ensures each super type instance belongs to exactly one sub type. These concepts allow complex databases to accurately model real-world scenarios where entities share common characteristics but have distinct properties and relationships. For exams, remember that sub types inherit from super types (not the reverse), and each sub type must justify its existence with at least one unique attribute.

🧠 Quick Revision Questions

1. What is the main difference between an ERD and an EERD?
2. Why are common attributes placed in a super type instead of duplicating them in each sub type?
3. According to the Exhaustive property, what happens if a sub type has no unique attributes of its own?
4. Under the Mutually Exclusive rule, can a single employee instance belong to both Full-time and Part-time sub types simultaneously?
5. In the HR System example, why can only Full-time employees access loans, and how is this modeled in the EERD?

📘 Lecture 11 — Example of Entity Relationship Diagram (ERD)

📖 Overview: This lecture demonstrates the complete process of converting real-world scenarios into Entity Relationship Diagrams (ERDs) and then into Physical Data Models. Through three detailed examples—Building-Apartment Renting, Music Album Production, and Airport Management—students learn how to derive entities, attributes, relationships, and finally generate physical database schemas with proper foreign keys and primary keys.

🗂️ Topics Covered

The lecture covers three comprehensive examples of ERD generation from textual scenarios. It explains how to identify entities and their attributes from descriptive narratives, how to model relationships between entities (One-to-Many, Many-to-Many, One-to-One), and the crucial step-by-step transformation of ERDs into Physical Data Models showing foreign keys, composite primary keys, and unique keys. The Building-Apartment example demonstrates complex relationships with composite keys, the Music example illustrates Many-to-Many resolution through junction tables, and the Airport example showcases One-to-One relationships with unique foreign keys.

📝 Lecture Summary

Example 01: Scenario and Generating ERD

This first example revisits the Building-Apartment Renting scenario from previous lectures. The scenario describes a building with multiple floors, each floor containing zero or more apartments. Customers can rent multiple apartments from the same building, but each apartment can be rented by at most one customer. At month-end, a receipt is generated against which rent is deposited.

From this scenario, the following entities and their attributes are identified:

• 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

Generating Physical Data Model

The lecture explains that in an ERD, it is not possible to show foreign keys as a rule, but in the Physical Data Model, it is possible to show all attributes and foreign keys explicitly. The following rules of thumb govern this transformation:

🔑 Definition — Foreign Key Rule: The primary key of the parent table is referred to as a foreign key in the child table in a One-to-Many relationship.

🔑 Definition — Many-to-Many Resolution: In a Many-to-Many relationship, a new third table (junction table) is introduced where the primary keys of both tables are written as foreign keys and made part of a Composite Primary Key (CPK).

🔑 Definition — One-to-One Rule: In a One-to-One relationship, any one table will contain a foreign key, but that foreign key must be made a Unique Key (UK) as a rule.

💡 Why this matters: These rules ensure referential integrity in the database and prevent data anomalies during insertion, deletion, and updates.

Physical Data Model of ERD (Example 01)

The Physical Data Model for the Building-Apartment scenario is:

• Building(bname, address)
• Floor(floor#, no_apt, bname)
  • Assumption: Floor# can be repeated among multiple buildings, therefore bname is made part of Composite Primary Key along with Floor# in the floor table.
• Customer(cid, name, address, contact#)
• Apartment(aid, status, floor#, bname, CA, cid)
• Receipt(rid, aid, cid, rent, dor)

📐 Composite Key Formula: (floor#, bname) → Together they uniquely identify a specific floor in a specific building.

📌 Example: If Building "A" has Floor 1 and Building "B" also has Floor 1, the composite key (1, "A") distinguishes the first from (1, "B").

Example 02: Scenario and Generating ERD

This second example involves musical bands, songs, writers, companies, and albums. The scenario states that musical bands record songs and request musical companies to launch their songs in the form of albums. Songs are written by song writers. An album can contain at least one song and a maximum of 12 songs. A writer can write multiple songs, but songs are usually written irrespective of the number of persons in the bands.

From this scenario, the following entities and attributes are identified:

• Bands: bname, total person, cat
• Songs: sname, duration, language
• Writer: wid, wname, charges, category
• Company: cname, address, phone#
• Album: aname, total_songs

Physical Data Model of ERD (Example 02)

The Physical Data Model for the Music scenario is:

• Band(bname, totalperson, cat)
• Company(cname, address, phone#)
• Songs(sname, duration, language, bname, aname)
• Writer(wid, wname, charges, category)
• Album(aname, total_songs, cname)
• Writer_song(wid, sname)

💡 Why this matters: The Writer_song table is a junction table that resolves the Many-to-Many relationship between Writer and Songs (a writer can write multiple songs, and a song can be written by multiple writers). Its composite primary key consists of (wid, sname).

Example 03: Scenario and Generating ERD

This third example involves an airport management system. The scenario describes several flights that are either landing or taking off from an airport. There is more than one passenger on a particular flight. Passengers are allocated seat numbers when they purchase tickets. A plane can be associated with different flights during its lifecycle. Passengers can order different meals during a flight (either Asian or Non-Asian). The airport is managed by a manager.

From this scenario, the following entities and attributes are identified:

• Airport: Aname, capacity, annual traffic
• Manager: Employee ID (eid), Airport Name (aname), name
• Flight: Flight ID (fid), Date of Departure (dod), Time of Dep. (tod), destination, aname
• Passenger: Passenger ID (pid), pname, fid, contact, address, date
• Ticket: Flight ID (fid), Ticket ID (tid), Passenger ID (pid), seat number, meal
• Plane: Plane ID (pid), capacity, age

Generating Physical Model from ERD (Example 03)

The Physical Data Model for the Airport scenario is:

• Airport(aname, capacity, annual traffic)
• Manager(eid, aname, name)
  • Note: aname is both FK and UK due to One-to-One relationship with Manager.
• Flight(fid, dod, tod, destination, aname)
• Passenger(pid, pname, fid, contact, address, date)
• Ticket(fid, tid, pid, seat#, meal)
• Plane(pid, capacity, age)
• Plane_flight(pid, fid)
  • Note: This resolves the Many-to-Many relationship between Flight and Plane.

📐 Relationship Pattern: One-to-One → Manager to Airport (aname is UK); Many-to-One → Flight to Airport; One-to-Many → Flight to Passenger; Many-to-Many → Flight to Plane (resolved via Plane_flight junction table).

📌 Example: A specific flight (fid = "PK301") can use different planes on different days. The Plane_flight table stores each assignment as a separate row with (pid, fid) as the composite key.

⭐ Key Takeaways

The most critical concepts from this lecture are the three rules for converting relationships from ERD to Physical Data Model: in One-to-Many relationships, the parent's primary key becomes a foreign key in the child table; in Many-to-Many relationships, a new junction table is created containing both primary keys as a composite primary key; and in One-to-One relationships, one table contains a foreign key that must be made unique. Additionally, composite keys are necessary when attributes like floor numbers can repeat across different parent entities (e.g., multiple buildings each having a floor 1). The Physical Data Model is the final step before database implementation and includes all attributes and explicit foreign key declarations that are absent from the conceptual ERD.

🧠 Quick Revision Questions

1. What is the rule for handling foreign keys when converting a One-to-Many relationship from an ERD to a Physical Data Model?
2. In the Building-Apartment example, why is bname included with floor# as a composite primary key in the Floor table?
3. How do you resolve a Many-to-Many relationship between Flight and Plane in the Physical Data Model? Name the resulting table and its primary key structure.
4. In the Airport scenario, which relationship type exists between Manager and Airport, and what constraint is placed on the foreign key (aname) in the Manager table?
5. What is the purpose of the Writer_song table in the Music Band example, and what foreign keys does it contain?

📘 Lecture 12 — Anomalies

📖 Overview: This lecture defines database anomalies as problems arising from poor planning and design, typically when data is stored in a single table instead of multiple tables. Understanding anomalies is critical because they lead to data redundancy, inconsistency, and serious performance issues, especially in large databases.

🗂️ Topics Covered

The lecture begins by defining what an anomaly is and why it is critical for database performance and user experience. It then explains the three specific types of anomalies: Insert Anomaly, Delete Anomaly, and Update Anomaly, providing examples of each to illustrate the problems caused by poor design.

📝 Lecture Summary

A. What is Anomaly?

Anomalies are defined as the problems that can occur due to poor planning and designing of databases. This usually occurs when a single table is created instead of creating multiple tables. An anomaly is an irregularity, or something which deviates from the expected or normal state.

🔑 Definition — Anomaly: an irregularity, or something which deviates from the expected or normal state, caused by poor database planning and design, often from using a single table instead of multiple tables.

B. Why Anomalies are Critical

Anomalies are critical as they can affect the end user experience. Furthermore, duplication or redundancy in the data is increased due to anomalies, which in turn creates other serious issues. Anomalies may not have a huge impact on small databases, but a database with a large data size may have serious performance issues caused by anomalies.

C. Insert Anomaly

An Insert Anomaly occurs when certain data cannot be entered without entering unwanted data. In other words, some attributes cannot be entered without the presence of some other attributes. This results from the poor design decision of storing data in just one table instead of multiple tables. For example, if we want to enter a new product (pid, pname, price), we have to add a customer (cid) against the product, which might not be possible at the time of entering the product. This means to add a wanted row (product), we have to enter an unwanted row (customer) because CID is the primary key and it cannot be left null.

🔑 Definition — Insert Anomaly: occurs when certain data cannot be entered into the database without also entering unwanted or unrelated data. 📌 Example: To add a new product (pid, pname, price), a customer (cid) must also be entered because CID is the primary key and cannot be null, forcing unwanted data insertion.

D. Delete Anomaly

This anomaly occurs when data is lost due to the deletion of some other data. The reasons for this anomaly are the same as for insert anomaly, i.e., poor design decisions. Due to this anomaly, certain attributes are lost due to the deletion of other attributes. For instance, if we want to delete pid: 4, we will also be deleting cid=2 because when we delete a complete row, the only record for cid=2 is also removed. This means we might end up losing customer data when we delete product data, which is an unwanted loss of data.

🔑 Definition — Delete Anomaly: occurs when the deletion of some data unintentionally causes the loss of other, unrelated data. 📌 Example: Deleting a product with pid: 4 also deletes the only customer record with cid: 2, resulting in the loss of customer data.

E. Update Anomaly

An Update Anomaly exists when one or more instances of duplicated data are updated, but not all. An update anomaly occurs when the same data item has to be updated more than once, which can lead to errors and inconsistency of data. The same information can be expressed on multiple rows; therefore, all the instances must be updated. If this is not done properly, an update anomaly arises. If there are multiple records and we fail to update all the instances, it will result in data-inconsistency across the database, leading to wrong reporting of data, which is unacceptable. For example, in the table, if we decide to update the Cname of cid=3 and fail to update all the rows containing that Cname, the system will report two different Cname values against the same CID=3, which is incorrect.

🔑 Definition — Update Anomaly: exists when one or more instances of duplicated data are updated, but not all, leading to data inconsistency. 📌 Example: Updating the Cname for cid=3 in only some records, but not all, results in the system showing different names for the same customer ID, causing inconsistency.

⭐ Key Takeaways

The most critical point from this lecture is that database anomalies arise from poor design, particularly from storing all data in a single table instead of normalizing into multiple tables. The three types of anomalies—Insert, Delete, and Update—each cause specific problems: insert anomaly forces you to add unwanted data, delete anomaly causes unintended data loss, and update anomaly leads to data inconsistency. These issues not only degrade user experience but also cause serious performance problems in large databases by increasing data redundancy and requiring multiple updates.

🧠 Quick Revision Questions

1. What is the definition of a database anomaly?
2. Why are anomalies considered critical for large databases?
3. What is an Insert Anomaly, and what design flaw causes it?
4. Describe a scenario where a Delete Anomaly would cause unintended data loss.
5. How does an Update Anomaly lead to data inconsistency?

📘 Lecture 13 — Normalization

📖 Overview: This lecture introduces database normalization as the process of efficiently storing data to reduce redundancy and eliminate undesirable characteristics. It covers the three normal forms (1NF, 2NF, 3NF) through practical examples, demonstrating how to transform unorganized data into a consistent, anomaly-free database structure.

🗂️ Topics Covered

The lecture covers normalization basics and objectives, normalization through reports using physical data models, definitions of repeating and non-repeating groups, detailed explanation of First Normal Form (1NF) with student-course example, Second Normal Form (2NF) addressing partial dependencies, Third Normal Form (3NF) eliminating transitive dependencies, and a second comprehensive example using flight-passenger-airport data.

📝 Lecture Summary

A. Normalization Basics

Database normalization is the process of storing data efficiently to reduce data redundancy and undesirable characteristics from the system. The two main objectives are to reduce redundancy and ensure logical relationships/dependencies, which is why related data is stored together. This process serves as a solution to database anomalies by removing all anomalies and bringing the database to a consistent state.

💡 Why this matters: Without normalization, databases suffer from update anomalies, insertion anomalies, and deletion anomalies that corrupt data integrity.

T. Normalization through Reports

Normalization can be done through physical data model or through reports. This course focuses on normalization through reports because it provides a clear structure of the whole process.

The process of normalization consists of three steps called Normal Forms:

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)

U. First Normal Form (1NF)

First normal form (1NF) sets the most basic rules for an organized database. As per First Normal Form, no two rows of data must contain repeating group of information i.e., each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row. Each table should be organized into rows, and each row should have a primary key that distinguishes it as unique.

These rules can be summarized as:

• Data should be organized in tables
• There should be no repeating groups
• There should be Primary Keys in each table
• Primary key non-repeating group should be written with every following group as FK
• First group has to be non-repeating group

V. Definition of Non-Repeating Group

Non-repeating group: Group in which there is only one row in the given scenario. In the student example, student is a non-repeating group as there is only one row given in the report.

W. Definition of Repeating Group

Repeating group: Group in which there are more than one row in the given scenario. In the student example, course and semester are repeating groups as there are multiple rows given in the report.

L. Example of First Normal Form (1NF)

Based on Figure 24, the following 1NF entities and attributes are defined:

The primary key of the first group (non-repeating group – Student) has to be written as FK with every other following group (Course and Semester). Just like One-to-Many relationships, primary key of parent table is written as FK in child table; Student is a parent table and Course & Semester tables are child tables.

First Normal Form (1NF)

The data presented in the above format fulfills all criteria of being in first normal form.

Y. Second Normal Form (2NF)

2NF is derived from 1NF, meaning the prerequisite to perform 2NF is 1NF. The rule states there must not be any partial dependency of any non-key column on primary key. The objective of second normal form is to take data that is only partly dependent on the primary key and enter that data into another table.

Z. Example of Second Normal Form (2NF)

Considering the data from Figure 24, the following conditions must be fulfilled:

• First Normal Form (1NF) is achieved
• There should be no partial dependency
• Each non-key attribute should be fully-functional dependent on Key

First Normal Form (1NF)

Second Normal Form (2NF)

AA. Third Normal Form (3NF)

Third Normal Form applies that every non-prime attribute of table must be dependent on primary key, or there should not be the case that a non-prime attribute is determined by another non-prime attribute. This is known as transitive dependency. This transitive dependency should be removed from the table, and the table must be in Second Normal Form.

BB. Example of Third Normal Form (3NF)

Continuing with the same example (1NF and 2NF already achieved):

Third Normal Form (3NF)

After applying normalization up to Third Normal Form (3NF), the data is in the following shape:

CC. Second Example of Normalization

Consider Figure 25 data with the following entities:

• Flight
• Passenger
• Airport

DD. Non-repeating group:

i. Flight

EE. Repeating Groups:

i. Passenger ii. Airport

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

After applying normalization up to 3NF, the data is in the following shape:

⭐ Key Takeaways

Normalization is a systematic process with three successive forms where each form builds upon the previous one. First Normal Form eliminates repeating groups and ensures atomic values with primary keys. Second Normal Form removes partial dependencies by ensuring all non-key attributes are fully functionally dependent on the entire primary key, requiring table decomposition when composite keys exist. Third Normal Form eliminates transitive dependencies where non-key attributes determine other non-key attributes, further decomposing tables to ensure every non-prime attribute depends only on the primary key. The end result is a consistent database structure free from update, insertion, and deletion anomalies.

🧠 Quick Revision Questions

1. What are the three normal forms in database normalization, and what specific problem does each form address?
2. In the student-course example, why was the Course table not in 2NF, and how was it transformed into 2NF?
3. What is a transitive dependency, and how was it demonstrated in the Student table with Total_Registered_Courses and Status?
4. In the flight-passenger-airport example, what partial dependency existed in the Airport table, and what two tables replaced it?
5. After completing 3NF normalization in the flight example, what new table was created to resolve the transitive dependency involving origin, destination, and distance?

📘 Lecture 14 — Denormalization

📖 Overview: This lecture introduces denormalization, the systematic process of adding redundancy to a database to improve performance by reducing the number of joins required in queries. It explains when and why denormalization is needed, contrasts it with normalization, and provides several practical scenarios for its application.

🗂️ Topics Covered

The lecture defines denormalization and its purpose, discusses the need for denormalization when running time is more critical than database structure, compares normalization vs. denormalization, outlines when to denormalize, and presents specific scenarios including storing end dates, storing details in master tables, implementing short circuit keys, using current indicator columns, and storing calculated values.

📝 Lecture Summary

A. What is Denormalization?

Denormalization is the process of systematically adding redundancy in a database with the purpose of improving database performance. The tables are merged into each other to group data, because the more tables you have, the more joins you have to perform in queries. Joins have a negative impact on performance and more joins mean more time is required to retrieve data, because data is required from multiple tables. The purpose of denormalization is to reduce the running time, the time it takes the DBMS to calculate results.

🔑 Definition — Denormalization: The process of systematically adding redundancy to a database to improve performance by reducing joins.

💡 Why this matters: Denormalization sacrifices normalization principles for speed, which is essential in high-performance systems where query response time is critical.

B. Need for Denormalization

Denormalization comes in as a solution if the running time of a database is of more importance than the database structure. In a large database, there are cases which require data from multiple tables in a single query, and the application may need to perform this type of query hundreds of times in a minute. In such a situation, a fully normalized database may be unacceptably slow. In such cases, denormalization of the database increases performance. So denormalization is a tradeoff between database structure and running time.

C. Normalization vs. Denormalization

The difference between normalization and denormalization is in the focus of these two activities. Normalization focuses on decreasing the data redundancy in a database, while the purpose of denormalization is to decrease the running time of a database. As normalization reduces inconsistency, the database designer needs to ensure that the denormalized database does not become inconsistent.

🔑 Definition — Normalization vs. Denormalization: Normalization reduces redundancy; denormalization reduces running time. Both are tradeoffs.

D. When to Denormalize?

Denormalization does not have any fixed rule like there are rules in normalization. It is totally scenario or need-based activity. As mentioned above, when the requirement is to reduce the running time of a database system, denormalization is done. It should be performed only if performance issues indicate that it is needed.

E. Scenarios for Denormalization

a. Storing End Date

The most common denormalization decision is to store the end date for periods that are consecutive; then the end date for a period can be derived from the start date of the previous period. It is appropriate when queries are needed from tables with long lists or records that are historical and the user is interested in the most current record. Add an end date column to speed up queries so that they can use a between operator, which is one of the most efficient operators that can be used to retrieve data and requires start and end range.

🔑 Definition — Storing End Date: Adding an end date column to speed up queries that filter using the BETWEEN operator for consecutive periods.

📌 Example: For historical records with start dates, instead of deriving end date from previous record's start date, add a separate end date column to allow direct BETWEEN queries.

b. Storing Details in Master Table

In a situation where the number of detail records in the child table per master record is fixed (or has a fixed maximum) and where usually all detail records are queried with the master, you may consider adding the detail columns to the master table. This denormalization works best when the number of records in the detail table is small. This way you will reduce the number of joins during queries. The child table will be removed and merged with the parent table. As this denormalization occurs, an anomaly will be created.

🔑 Definition — Storing Details in Master Table: Merging a fixed or limited number of detail records into the master table to eliminate joins.

c. Example of Storing Details in Master Table

The phenomenon of storing details in the master table can be illustrated with the help of the following figure: Considering the image, the normalization of the database resulted in two separate tables for Order and Order Details as shown in the figure. In order to speed up the query processing time, these two tables will be merged together i.e. denormalization. In this way, no joins will be required, saving space and speeding up the query processing time and ultimately improving the database performance.

📌 Example: Merge Order and Order Details tables into a single table, eliminating the join between them. This speeds up queries but introduces data redundancy and potential anomalies.

d. Short Circuit Key

The short circuit key ensures the relationships between the required entities. In a database that contains multiple levels of master tables and there is a need to query the lowest and highest level records only, consider creating short-circuit keys. These new foreign key definitions directly link the lowest level detail records to higher-level grandparent records. The result can produce fewer table joins when queries execute. It is appropriate when queries frequently require values from a grandparent and grandchild, but not from the parent.

🔑 Definition — Short Circuit Key: A direct foreign key link between grandchild and grandparent tables, bypassing the parent table to reduce joins.

e. Example of Short Circuit Key

As per the following example, before denormalization of the database, all entities have parent-child relationship but no grandparent-grandchild relationship exists. In certain scenarios where grandparent-grandchild is required to process the query, a short circuit key comes in as a solution. As shown in the figure below, after denormalization, grandparent-grandchild also exists in the database. To achieve this, extra foreign keys will be required.

📌 Example: In a structure with Table A (grandparent) → Table B (parent) → Table C (grandchild), add a direct foreign key from Table C to Table A so queries about C and A don't need to join through B.

f. Current Indicator Column

This type of denormalization can be used in similar situations to the end date column technique. It can even be used in addition to an end date. It is a very common type of denormalization. Suppose most queries are to find the most current detail record. With this type of requirement, you could consider adding a new column to the details table to represent the currently active record. In simple words, frequently updated columns should have a last-updated timestamp and a status column is added to show updated or old record.

🔑 Definition — Current Indicator Column: A status column added to a table to identify which record is currently active, speeding up queries for the most recent data.

g. Example of Current Indicator Column

Consider a table where the stock price of a company is being recorded. The stock price changes very frequently and the database also records the last time of update. But as the records change frequently, getting the latest stock price is difficult without having a status column in the table. This is where a current indicator column is added (as a result of denormalization) with the motive of having status of all records. This column will tell whether a specific record/stock price is the latest one or not.

📌 Example: In a stock_price table, add a column "is_latest" (Y/N) to instantly identify the most recent stock price without scanning all records.

h. Storing Calculated Value

When a calculation is frequently executed during queries, it can be worthwhile storing the results of the calculation. Queries that require calculation require more time to execute, and to reduce this running time, calculated fields should be pre-stored in the table. Through this type of denormalization, the calculation does not need to be performed during a query and the source values do not need to be looked up every time the calculated value is required. As a result, query processing time will be shortened.

🔑 Definition — Storing Calculated Value: Pre-computing and storing frequently requested calculated values to avoid runtime computation during queries.

i. Example of Storing Calculated Value

Consider a database keeping records of cars with engine number, fuel tank size, and average fuel consumption per liter. A query requiring total kilometers that can be traveled will require calculation which will ultimately increase the processing time. After denormalization, a column is added calculating the number of kilometers that a car can travel as per its fuel capacity. In this way, the calculation is pre-stored and the calculation will not be required during query processing, reducing running time.

📌 Example: For a car table with columns fuel_tank_size (liters) and avg_consumption (km/liter), add a column total_range = fuel_tank_size * avg_consumption so queries for total kilometers don't need runtime calculation.

⭐ Key Takeaways

Denormalization is a performance-tuning technique that trades data redundancy for faster query execution by reducing the number of joins. It should only be applied when specific performance issues indicate a need, not as a default design approach. The five key denormalization scenarios are: storing end dates for BETWEEN queries, merging fixed-size detail tables into master tables, using short-circuit keys to skip intermediate tables, adding current indicator columns for frequently updated records, and storing pre-computed calculated values to avoid runtime calculations. Each scenario introduces potential anomalies and inconsistency risks that must be managed. Denormalization is always a tradeoff between database structure integrity and query running time.

🧠 Quick Revision Questions

1. What is denormalization and what is its primary purpose?
2. How does denormalization differ from normalization in terms of focus?
3. In which scenario would you use a short circuit key, and what problem does it solve?
4. How does storing a current indicator column improve performance in frequently updated tables?
5. What is the tradeoff involved when storing calculated values in a table instead of computing them at query time?

📘 Lecture 15 — Introduction to Oracle 11g on Cloud

📖 Overview: This lecture introduces Oracle 11g as a cloud-hosted database service, explaining how it provides full Oracle Database features without requiring hardware maintenance, backup knowledge, or software upgrades. It provides step-by-step instructions for logging into the Oracle Cloud environment and accessing SQL commands to begin database management and development.

🗂️ Topics Covered

The lecture covers two main sections: an introduction to Oracle 11g as a cloud service hosted by Oracle, providing full database features without hardware or backup responsibilities; and a detailed step-by-step login procedure for accessing Oracle 11g on the cloud, including navigating the workspace and using SQL commands.

📝 Lecture Summary

B. Introduction

This section introduces Oracle 11g on Cloud as a service hosted entirely by Oracle. Users get full access to all features and operations available with Oracle Database, but Oracle manages the virtual machine (VM) and cloud storage. This means you can perform all database management and development operations without purchasing and maintaining hardware, without knowing backup and recovery commands, and without performing complex tasks like database software upgrades and patching.

🔑 Definition — Oracle 11g on Cloud: A cloud service hosted by Oracle that provides full access to Oracle Database features, with Oracle managing the VM and cloud storage, allowing users to focus on database management and development without hardware or maintenance responsibilities.

💡 Why this matters: This service removes the burden of infrastructure management, making database work accessible to users without system administration expertise.

C. Login to Cloud

This section provides a step-by-step guide to accessing Oracle 11g on the cloud.

Step 01: Follow the address https://apex.oracle.com/en/ to access Oracle 11g.

Step 02: Click on the Sign In button on the upper right corner of the page. Enter the following credentials:

• Workspace Name
• Username
• Password

Step 03: Click on the SQL Workshop tab at the top-mid of the page.

Step 04: Click on the SQL Commands tab from the drop-down list to access the code or enter SQL Statements/Commands, then click Run to see the results. Click on the History Tab to access saved code.

📐 Formula (Procedure): Login Steps: 1. Go to https://apex.oracle.com/en/ → 2. Sign In with Workspace Name, Username, Password → 3. Click SQL Workshop → 4. Click SQL Commands → 5. Enter SQL and Run → 6. Use History Tab for saved code.

📌 Example: To run a SQL command, after logging in, navigate to SQL Workshop > SQL Commands, type SELECT * FROM employees; in the code area, click Run, and the results will display below. To retrieve previously run commands, click the History Tab.

⭐ Key Takeaways

This lecture introduces Oracle 11g as a fully managed cloud database service, eliminating the need for hardware, backup expertise, and software patching. The login procedure is a straightforward four-step process: accessing the Oracle apex website, signing in with workspace credentials, navigating to SQL Workshop, and using SQL Commands to execute statements. The History Tab allows users to access previously saved code, enabling efficient workflow. For exam purposes, remember that Oracle hosts the VM and storage, and that SQL Workshop contains the SQL Commands tab for writing and running queries. The key advantage is that all complex maintenance tasks are handled by Oracle.

🧠 Quick Revision Questions

1. What are the key responsibilities Oracle handles in the Oracle 11g Cloud service?
2. List the four main steps to log into Oracle 11g on the cloud.
3. What three credentials are required to sign in to Oracle 11g Cloud?
4. After logging in, which tab do you click to access SQL Commands?
5. Where can you find previously executed SQL commands?

📘 Lecture 16 — Using Data Definition Language (DDL) in Oracle 11g

📖 Overview: This lecture introduces Data Definition Language (DDL) in Oracle 11g, focusing on how to create, alter, and drop database objects like tables. Understanding DDL is essential because it forms the foundation for storing and retrieving data in any relational database system.

🗂️ Topics Covered

The lecture covers the general syntax of the CREATE TABLE statement, creating a single table using an ERD example, creating tables with foreign keys and composite primary keys, the ALTER TABLE command for adding columns or modifying data types, and the DROP TABLE command with proper table dropping sequence to maintain referential integrity.

📝 Lecture Summary

A. Syntax of DDL

Data Definition Language (DDL) is used to create database objects in the Database. Every database object has a Data Dictionary created and managed by the System. The most important database object is the Table, which is required for storage and retrieval of data. DDL includes Create, Alter, and Drop statements.

The general syntax of the CREATE TABLE statement is:

Create Table TableName (Column datatype [null | not null], ....)

Common data types include: i. Number – for numeric values ii. VarChar2 – Variable Length Character (stores text) iii. Date – stores dates in DD/Mon/YYYY format

🔑 Definition — DDL (Data Definition Language): A subset of SQL used to define and manage database structures, including creating, altering, and dropping database objects.

📐 Formula/General Syntax:

Create Table TableName (Column datatype [null | not null], ....)

→ This creates a new table with specified column names, their data types, and optional null/not null constraints.

B. Creating Single Table

The lecture provides an example ERD (Entity Relationship Diagram) showing the "Building" table. This table becomes a single table implementation in the database.

💡 Why this matters: Creating single tables is the first step in database implementation — each entity from an ERD typically becomes one table.

📌 Example: DDL statement to implement the Building Table:

Create table Building (
  bname varchar2(30) primary key, 
  address varchar2(40), 
  phone number number(10)
);

This creates a table named "Building" with three columns: bname (set as the primary key), address, and phone number.

C. Creating Table with Foreign Key

In the Floor table, bname is a foreign key that references the Building table. Floor# and bname together form a Composite Primary Key for the Floor table.

🔑 Definition — Composite Primary Key: A primary key that consists of two or more columns combined to uniquely identify each row in a table.

🔑 Definition — Foreign Key: A column or set of columns in one table that references the primary key of another table, maintaining referential integrity.

📌 Example: Code to implement the Floor table with foreign key and composite primary key:

Create table Floor (
  floorno number(10), 
  no_of_apt number(10), 
  bname varchar2(30) references building(bname), 
  primary key (floorno, bname)
);

This creates the Floor table where bname references the Building table's primary key, and the combination of floorno and bname serves as the composite primary key.

D. Alter Table Command

The ALTER TABLE command is used to either add a new column or modify an existing column's data type or size.

📌 Example 1: Adding a new column "rent" to the Building table:

Alter table Building add (rent number(10));

📌 Example 2: Modifying the data type of "phoneno" from number to varchar2:

Alter table Building modify (phoneno varchar2(10));

🔑 Definition — ALTER TABLE: A DDL command used to modify the structure of an existing table by adding, modifying, or dropping columns.

E. Drop Table Command

To drop a table from the database, the DROP TABLE command is used. It is critical to maintain the correct sequence when dropping tables — all child tables must be dropped first, and then the parent table can be dropped.

🔑 Definition — Parent Table: A table whose primary key is referenced by a foreign key in another (child) table. 🔑 Definition — Child Table: A table that contains a foreign key referencing another (parent) table's primary key.

📌 Example: Correct sequence to drop tables:

Drop table floor;     -- Child table dropped first
Drop table building;  -- Parent table dropped second

💡 Why this matters: Dropping a parent table before its child table would violate referential integrity constraints and cause errors. Always drop child tables first.

⭐ Key Takeaways

The most critical concepts from this lecture are: DDL consists of CREATE, ALTER, and DROP statements for managing database objects. The CREATE TABLE command requires specifying column names, data types (NUMBER, VARCHAR2, DATE), and constraints like PRIMARY KEY and FOREIGN KEY. Composite primary keys are formed using multiple columns, while foreign keys maintain relationships between tables. ALTER TABLE allows adding new columns or modifying existing column properties. When dropping tables, always drop child tables (those with foreign keys) before parent tables to avoid referential integrity errors.

🧠 Quick Revision Questions

1. What are the three main DDL commands discussed in this lecture?
2. What is the difference between a simple primary key and a composite primary key?
3. In the Floor table example, why is "bname" both a foreign key and part of the composite primary key?
4. What is the correct sequence for dropping the Building and Floor tables, and why?
5. How would you add a new column called "construction_year" with a DATE data type to the Building table?

📘 Lecture 17 — Using Data Manipulation Language (DML) and Data Control Language (DCL)

📖 Overview: This lecture introduces the fundamental Data Manipulation Language (DML) commands — INSERT, UPDATE, DELETE, and TRUNCATE — used to manage data within database tables. It also covers Data Control Language (DCL) concepts through transaction control using COMMIT and ROLLBACK statements, which are essential for maintaining data integrity.

🗂️ Topics Covered

The lecture covers the Insert Statement syntax for adding data to tables, including limited column insertion; the Update Table syntax for modifying single or multiple columns with conditions; the Delete Statement for removing rows; the Truncate Table command for faster removal of all rows; and Transaction basics including Commit and Rollback statements for saving or undoing database changes.

📝 Lecture Summary

A. Insert Statement Syntax

The insert statement is used to add data into one or more tables in a database. Every column in the list-of-columns must have a valid value in the corresponding position in the list-of-values. Before inserting a row, you must know the table's columns and their valid values. Referential Integrity constraints are implemented by default.

🔑 Definition — Insert Statement: A DML command used to add new rows of data to a table. 📐 Syntax: INSERT into TableName VALUES (value 1, value 2...); → Adds a complete row with values for all columns. 📐 Syntax (with columns): INSERT into TableName (column1, column2) VALUES (value1, value2); → Adds values only to specified columns. 📌 Example: INSERT into building VALUES ('Test', 'VU', 3948283); inserts a new building record. INSERT into building (bname, address) VALUES ('Test-1', 'VU -LRO'); inserts only building name and address.

KKKKKK. Viewing Data from Table

To verify the insertion of data in a table, the following query can be used: Select * from building;. Detailed discussion on the SELECT statement will be covered later in the course.

LLLLLL. Limited Column Data Insertion

If values for only a limited number of columns are known, then limited values can be added to the table. However, the value of the Primary Key column cannot be left NULL.

MMMMMM. Update Table Syntax

The Update command is used to modify one or more columns of a table. Each value must be valid for its column-name. If the WHERE clause is included, the statement updates column values only in rows that satisfy the condition.

🔑 Definition — Update Statement: A DML command used to modify existing records in a table. 📐 Syntax: UPDATE TableName SET column1 = value, column2 = value WHERE condition; → Changes specified column values in rows matching the condition. 💡 Why this matters: Without a WHERE clause, ALL rows in the table will be updated, which can cause unintended data changes.

NNNNNN. Implementing Single Column Update Statement

📌 Example: UPDATE emp SET ename='Anders' WHERE empno=7369; changes the employee name to "Anders" for employee number 7369. Verification: SELECT * from emp;

OOOOOO. Implementing Multiple Column Update Statement

📌 Example: UPDATE emp SET job='MANAGER', sal=2000 WHERE empno=7369; updates both the job title and salary columns for employee number 7369. Verification: SELECT * from emp;

PPPPPP. Delete Statement Syntax

The Delete statement is used to remove one or more rows from a table. The deleted rows can be rolled back if not committed. Being committed means all changes in the database have been made permanent using the Commit command. The Rollback command is used to restore changes made in the database to the way they were before (this only works if you haven't already used Commit). Delete command can also be used with a condition to delete a particular row.

🔑 Definition — Delete Statement: A DML command that removes rows from a table, with the possibility of rollback if not committed. 📐 Syntax (all rows): DELETE FROM TableName; → Removes all rows from the table. 📐 Syntax (specific rows): DELETE FROM TableName WHERE condition; → Removes only rows satisfying the condition.

QQQQQQ. Implementing Delete Statement

📌 Example: DELETE FROM emp WHERE empno=7369; deletes the employee with employee number 7369. DELETE FROM emp; will delete all rows from the table emp.

RRRRRR. Truncate Table

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers and other dependencies.

🔑 Definition — Truncate: A DDL command that removes all rows from a table while preserving the table structure, faster than DELETE but cannot be rolled back.

SSSSSS. Transaction Basics

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether manually by a user or automatically by a database program. A transaction is the propagation of one or more changes to the database. For example, creating, updating, or deleting a record from a table means you are performing a transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

TTTTTT. Commit Statement

The Commit Statement is used to save the changes made in the database. It is the transactional command used to save changes invoked by a transaction to the database. The Commit Statement saves all transactions to the database since the last Commit or Rollback Statement. The DML statements are not auto-commit and should be saved by giving the commit statement.

🔑 Definition — Commit: A transaction control command that makes all changes permanent and visible to other users.

UUUUUU. Rollback Statement

The Rollback Statement is the transactional command used to undo transactions that have not already been saved to the database. The Rollback Statement can only be used to undo transactions since the last Commit Statement or Rollback Statement was issued. In simple words, it is used to undo the work performed by the current transaction.

🔑 Definition — Rollback: A transaction control command that reverts the database to its state before the last Commit or Rollback statement.

⭐ Key Takeaways

The four core DML commands covered are INSERT, UPDATE, DELETE, and TRUNCATE — with INSERT requiring valid values for each column and never allowing NULL in a Primary Key; UPDATE allowing single or multiple column modifications based on a condition; DELETE being rollback-able if not committed; and TRUNCATE being faster but irreversible. Transaction control is managed through COMMIT (permanently saves changes) and ROLLBACK (undoes uncommitted changes), with the critical rule that DML statements are not auto-committed and require an explicit COMMIT to make changes permanent. Finally, without a WHERE clause, UPDATE and DELETE commands affect all rows in the table.

🧠 Quick Revision Questions

1. What is the difference between the DELETE and TRUNCATE commands in terms of rollback capability and speed?
2. What happens if you execute an UPDATE statement without a WHERE clause?
3. Can you insert a NULL value into a Primary Key column during limited column data insertion?
4. What is the purpose of the COMMIT statement, and why is it necessary after DML operations?
5. Explain what the ROLLBACK statement does and under what conditions it can be used to undo changes.

📘 Lecture 18 — Structured Query Language (SQL) Basics

📖 Overview: This lecture introduces Structured Query Language (SQL), the standard language for managing and retrieving data in relational databases. It covers fundamental SQL syntax, filtering with WHERE clause, logical operators, pattern matching with LIKE and wildcards, and advanced query capabilities including GROUP BY, HAVING, and ORDER BY clauses.

🗂️ Topics Covered

SQL basics including DDL, DML, DRL, and DCL components; SQL syntax with SELECT, DISTINCT, and WHERE clause implementation; logical operators (AND, NOT, OR) with truth tables; wildcard characters (%) and (_) with LIKE operator for pattern matching; IN and BETWEEN operators; single-row functions (ROUND, TRUNC, LENGTH, INSTR, CONCAT); multiple-row/group functions (COUNT, SUM, MIN, MAX, AVG); GROUP BY clause for grouping data; HAVING clause for filtering grouped results; ORDER BY clause for sorting; date handling and date functions.

📝 Lecture Summary

A. Structured Query Language (SQL)

SQL is a database computer language designed for the retrieval and management of data in relational databases. SQL stands for Structured Query Language. SQL consists of a data definition language (DDL) , data manipulation language (DML) , data retrieval language (DRL) and a data control language (DCL) . The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control.

XXX. SQL Syntax

All SQL statements start with keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all statements end with a semicolon (;) .

SELECT DISTINCT * ColumnName FROM TableName;

• • means all the columns
• ColumnName is one or more column from table
• DISTINCT means unique values from column

WWWWWW. Implementing SQL

Sample SQL statements where capital words are SQL commands and words in capital letters are table names:

SELECT ename, job FROM emp WHERE job ='MANAGER'; SELECT ename, sal FROM emp WHERE sal<> 2000;

XXXXXX. SQL and WHERE Clause

The WHERE clause includes a condition that restricts the rows returned by the query. The WHERE clause eliminates all rows from the result set where the condition does not evaluate to True. From a large table, required rows can be fetched by using WHERE clause which applies to each row of the table. If the given condition is satisfied then only it returns specific value from the table. You would use WHERE clause to filter the records and fetch only necessary records.

YYYYYY. Implementing SQL and WHERE Clause

Example 01: Get list of all employees who are titled as managers: SELECT ename, job FROM emp WHERE job ='MANAGER';

Example 02: Get list of all employees whose salary is not equal to 2000: SELECT ename, sal FROM emp WHERE sal<> 2000;

ZZZZZZ. SQL and Logical Operators

Logical operators are used to specify multiple conditions in a single WHERE clause to retrieve rows based on values in multiple columns. AND, NOT and OR operators combine two or more conditions into a compound condition to get specific required results.

Truth Table:

AAAAAAA. Implementing Logical Operator 01

Scenario: Find list of all employee names earning more than 2500 but less than 5000. SELECT ename, sal FROM emp WHERE sal> 2500 AND sal< 5000;

BBBBBBB. Implementing Logical Operator 02

Scenario: Find all employees working in Dept # 20 with designation of Analyst. SELECT * FROM emp WHERE deptno=10 AND job='ANALYST';

CCCCCCC. Implementing Logical Operator 02

Scenario 01: Display list of all empno earning more than 400 as commission with designation of Manager. SELECT empno FROM emp WHERE comm> 400 and JOB='MANAGER';

Scenario 02: Find all salesmen working in dept # 30 with salary less than 1500. SELECT * from emp WHERE JOB='SALESMAN' AND deptno=30 and sal< 1500;

DDDDDDD. Wildcard Characteristics in SQL

Two wildcard characters are used to broaden selections: the percent sign (%) and the underscore (_) . The percent sign allows substitution of one or more characters in a field. The underscore allows substitution of a single character in an expression. Wildcards are used when part of the value to be searched is known, not the exact value.

• %: Zero or more characters
• _ : Exactly one character

EEEEEEE. Like Operator in SQL

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Wildcard operators (% and _) identify the patterns. The SQL LIKE condition allows you to use wildcards to perform pattern matching in the WHERE clause of SELECT, INSERT, UPDATE, or DELETE statements.

FFFFFF. LIKE Syntax

Basic syntax of LIKE clause: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern and column_name LIKE pattern;

LIKE with Percentage (%): SELECT FROM table_name WHERE column LIKE 'XXXX%'

LIKE with Underscore (_): SELECT FROM table_name WHERE column LIKE 'XXXX_'

GGGGGGG. Implementing Like – 01

Scenario: Display list of names of all employees who have either E in the name or the name should end with G with at least two characters. SELECT * FROM emp WHERE ename LIKE'%E%' OR ename LIKE'%-G';

HHHHHHH. Implementing Like – 02

Scenario: Display all information about employees having RE in the job with at least three characters and earning at least 2500 but at most 5000. Select * FROM emp WHERE job LIKE'%RE-%' AND sal> 2500 AND sal< 5000;

Practice Scenarios:

• Write a query to display all info about employees with at least one vowel in the ename
• Write a query to display list of employees earning more than 2000 but at most 5998 and having at least two occurrences of E in the ename

IIIIIII. IN Operator

The IN operator works like the OR Operator, allowing search for a value from a given list of values. SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);

JJJJJJJ. Implementing IN Operator

Scenario: Display all information about employees working as MANAGER, ANALYST, or CLERK, belonging to department number 20, earning at most 2500. SELECT * FROM emp WHERE job IN ('MANAGER', 'ANALYST', 'CLERK') AND deptno = 20 AND sal<=2500;

KKKKKKK. BETWEEN Operator

The BETWEEN operator selects values within a range; values can be numbers, text, or dates. The border values of the range are included in the search. The BETWEEN operator takes minimum and maximum values and searches between the two including the min and max values. Select * from table_name Where col_name BETWEEN val1 AND val2

LLLLLLL. Implementing BETWEEN Operator

Scenario: Display all information about employees earning at least 2975 and at most 5000, working in department 20, reporting to employee with empno 7839. SELECT * FROM emp WHERE sal BETWEEN 2975 AND 5000 AND deptno= 20 AND mgr = 7839

MMMMMMM. Single Row Function

Single row functions operate on single rows only and return one result per row. They accept one or more arguments and return one value for each row returned by the query. Single row functions can be character functions, numeric functions, date functions, and conversion functions. They can be used in SELECT and WHERE statements.

NNNNNNN. Implementing Single Row Function

ROUND and TRUNC: SELECT ROUND(194.683,1), TRUNC(194.683,1) FROM dual;

LENGTH, INSTR & CONCAT: SELECT LENGTH(ename), INSTR(ename, 'E'), CONCAT(ename,job) FROM emp;

💡 Why this matters: Single row functions allow you to transform and manipulate individual data values row by row, which is essential for data cleaning and formatting operations.

OOOOOOO. Group or Multiple Row Function

Group functions manipulate groups of rows to give one result per group of rows. They compute an aggregate value based on a group of rows. SELECT COUNT *, sum(sal), min(sal), max(sal), Avg(sal) FROM emp;

PPPPPPP. GROUP BY Clause

GROUP BY clause groups similar rows together to form groups, and the multiple row function is used with the GROUP BY clause. The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.

SELECT column1, column2 FROM table_name WHERE [conditions] GROUP BY column1, column2

QQQQQQQ. Implementing GROUP BY Clause 01

Situation: What is the salary paid of each department?

• Need to group together all rows of each department separately
• Need sum of salary for each department group SELECT SUM(sal), deptno FROM emp GROUP BY deptno;

RRRRRRR. Implementing GROUP BY Clause 02

Scenario: What is average and maximum salary paid to each Job who are reporting to MGR 7839? SELECT avg(sal), max(sal), job FROM emp WHERE mgr=7839 GROUP BY job

Note: Error occurs if job, mgr are not written after GROUP BY clause.

SSSSSSS. HAVING Clause

The HAVING clause is like the WHERE clause but filters results in aggregated/grouped data. The WHERE clause cannot be used in aggregated data, so SQL HAVING clause filters the results. The WHERE clause places conditions on selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

SELECT column1, column2 FROM table1, table2 GROUP BY column1, column2 HAVING [conditions]

TTTTTTT. Implementing HAVING Clause – 1

Scenario: Display average salary of each department if there are at least 2 employees working in the department.

• Group together rows of each department
• Calculate average salary for each department group
• Count number of employees in each group
• Display only groups with more than 2 rows SELECT avg(sal) FROM emp GROUP BY deptno HAVING count(*) > 3;

UUUUUUU. Implementing HAVING Clause – 2

Scenario: Display maximum and minimum salary by each department if average salary is more than 1500 and less than 3000. Exclude employees with any occurrence of 'A' in the ename. SELECT max(sal), min(sal) FROM emp WHERE ename NOT LIKE'%A%' GROUP BY deptno HAVING avg(sal) > 1500 AND avg(sal) < 3000;

VVVVVVV. ORDER BY Clause

The ORDER BY clause is used with the SQL SELECT statement to sort results in ascending or descending order. You specify one or more columns for sorting. The ORDER BY keyword sorts records in ascending order by default. Use the DESC keyword to sort in descending order.

SELECT column_name, FROM table_name ORDER BY column_name ASC|DESC

WWWWWWW. Date Handling

Date is format specific in SQL and uses a specific format (MM-DD-YY). Two dates cannot be added, subtracted, multiplied, or divided. Days can only be added to Date.

SELECT hiredate + 30, hiredate-50, sysdate, comm, sal, deptno FROM emp WHERE deptno=30 AND comm IS NOT null OR sal< 3000;

XXXXXXX. Date Functions

MONTHS_BETWEEN: Returns the number of months between date1 and date2 MONTHS_BETWEEN( date1, date2 )

ADD_MONTHS: Returns a date with a specified number of months added ADD_MONTHS( date1, number_months )

NEXT_DAY: Returns the first weekday that is greater than a date NEXT_DAY( date, weekday )

LAST_DAY: Returns the last day of the month based on a date value LAST_DAY( date )

Example: SELECT MONTHS_BETWEEN (sysdate, hiredate)/12, ADD_MONTHS (sysdate, hiredate), NEXT_DAY (sysdate, 'Wednesday'), LAST_DAY (sysdate) FROM emp;

⭐ Key Takeaways

SQL is a comprehensive language with four components: DDL, DML, DRL, and DCL, and all statements end with a semicolon. The WHERE clause filters rows based on conditions, and logical operators (AND, OR, NOT) combine multiple conditions with specific truth table behavior. Pattern matching uses LIKE with % (zero or more characters) and _ (exactly one character) wildcards, while IN and BETWEEN operators provide alternative filtering methods. Group functions (COUNT, SUM, MIN, MAX, AVG) work with GROUP BY to aggregate data, and the HAVING clause filters these aggregated groups (unlike WHERE which filters individual rows). Single-row functions operate per row while group/multiple-row functions operate on groups, and ORDER BY sorts results ascending by default or descending with DESC keyword.

🧠 Quick Revision Questions

1. What is the difference between WHERE and HAVING clauses in SQL, and when would you use each?

2. How does the LIKE operator with % wildcard differ from LIKE with _ wildcard? Provide an example of each.

3. What is the purpose of the DISTINCT keyword in a SELECT statement, and what would SELECT DISTINCT deptno FROM emp; return?

4. Explain the behavior of AND vs OR logical operators when one condition is TRUE and the other is FALSE.

5. What does the GROUP BY clause do, and why can't the WHERE clause be used to filter grouped data?

📘 Lecture 19 — Advance SQL

📖 Overview: This lecture covers advanced SQL techniques for combining data from multiple tables. It explores Cartesian products, various types of joins (inner, self, outer), and subqueries, explaining how each operation works and when to use them to retrieve meaningful information from relational databases.

🗂️ Topics Covered

This lecture begins with the mathematical concept of Cartesian products and demonstrates their application in SQL, including the pitfalls of using them without proper join conditions. It then moves to Inner Joins, explaining how they combine tables using common keys. The lecture continues with Self-Joins for recursive relationships and Outer Joins (Left and Right) to retain non-matching rows. Finally, it introduces Subqueries as an alternative to joins for dynamic criteria-based data selection.

📝 Lecture Summary

A. Cartesian Product

Cartesian product is mathematically a binary operation where two objects or sets (or tables) are combined in an “everything in combination with everything” fashion. In an SQL statement, a Cartesian product is where every row of the first table is joined with every row of the second table. Simply defining, the pairing of one element in a set with every element of the second set is called a Cartesian product.

🔑 Definition — Cartesian Product: A binary operation that pairs each element of one set with every element of another set, resulting in all possible combinations. 📐 Formula: Total Number of Pairs = No. of Elements in Set A * No. of Elements in Set B 📌 Example: For Set A = {2, 5, 6} and Set B = {8, 1}

• A * B = {(2,8), (2,1), (5,8), (5,1), (6,8), (6,1)}
• Total Pairs = 3 * 2 = 6

YYYYYYY. Cartesian Product & Joins

As mentioned above, Cartesian product is the pairing of one element in one set with every element of the other set. But, as relational databases are usually normalized, combining two tables creates another table that combines information from both tables. This will result in duplication in the data, losing data integrity. Joining tables without a proper condition will show wrong data.

📌 Example: Employee Database with EMP table (Empno, Ename) and DEPT table (Deptno, Dname)

• EMP rows: (1, Imran), (2, Kashif), (3, Asif)
• DEPT rows: (10, Sales), (20, Marketing), (30, Engineering)
• Output of Cartesian Product: 3 * 3 = 9 rows
• Every employee appears to be working in every department, which is WRONG information.

💡 Why this matters: A Cartesian product without a join condition produces meaningless, duplicated data that misrepresents real-world relationships.

ZZZZZZZ. Join or Inner Join

Joins are required when data from multiple tables is required. The standard join operation is known as an inner join. It horizontally combines two or more tables into a single working table. An inner join is performed by logically performing the Cartesian product (generating all combinations of rows) of the tables, then selecting only those rows that satisfy the join condition. A primary key field in one table can be a foreign key field in another table, and a join operation is used to combine tables using a common key in both tables.

🔑 Definition — Inner Join: A join operation that returns only rows from both tables that satisfy the specified join condition.

📌 Example: Basic Join Statement

SELECT empno, ename, d.deptno, dname
FROM emp e, dept d
WHERE d.deptno = e.deptno;

📌 Example: Join with Other Conditions

SELECT empno, ename, d.deptno, dname,
ROUND(months_between(sysdate, hiredate),0), hiredate
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND months_between(sysdate, hiredate) > 410;

📐 Formula for Self-Join Syntax:

SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

AAAAAAAA. Self-Join

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. In a self-join, a table is joined with itself, especially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table to itself means that each row of the table is combined with itself and with every other row of the table. Self-joins are used in a recursive relationship.

💡 Why this matters: Self-joins allow you to model hierarchical or recursive relationships like prerequisites (where a course's prerequisite must be a valid course itself), managers and employees, or parent-child relationships within a single table.

BBBBBBBB. Implementing Self-Joins

📌 Example 01: Find managers and their employees

SELECT e.ename, e.empno, b.ename, b.empno
FROM emp e, emp b
WHERE e.empno = b.mgr;

📌 Example 02: Consider a Customer Table with attributes: Customer (id, name, age, address, salary)

SELECT a.ID, b.NAME, a.SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;

CCCCCCCC. Outer Join

An outer join extends the result of a simple join. An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists, non-matching rows appear as NULL. The SQL OUTER JOIN returns all rows from both the participating tables which satisfy the join condition along with rows which do not satisfy the join condition. The SQL OUTER JOIN operator (+) is used only on one side of the join condition. Outer joins subdivide further into left outer joins and right outer joins, depending on which table's rows are retained.

🔑 Definition — Outer Join: A join that returns all rows from one or both tables, filling non-matching rows with NULL values.

DDDDDDDD. Left Outer Join

A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table, including rows with NULL (empty) values in the link field. All the rows from the left table will be included and all the matching rows from the right table will be included. A NULL value will be displayed for a non-matching value. Simply defining, the result of a left outer join (or simply left join) for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).

🔑 Definition — Left Outer Join: A join that returns all rows from the left table, with matching rows from the right table, and NULLs for non-matching right table columns.

EEEEEEEE. Implementing Left Outer Join

Suppose a table called suppliers with two fields (supplier_id and supplier_name) and another table called orders with three fields (order_id, supplier_id, and order_date).

📌 Example: Left Outer Join SQL Statement

SELECT supplier.sid, supplier.sname, orders.doe
FROM supplier, orders
WHERE supplier.sid = orders.sid(+);

The (+) operator on the right side (orders.sid) indicates a left outer join, meaning all rows from the left table (supplier) are retained.

The result set includes rows for Microsoft and NVIDIA even if they have no matching orders, with the order_date field containing NULL for these two.

FFFFFFFF. Right Outer Join

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.

🔑 Definition — Right Outer Join: A join that returns all rows from the right table, with matching rows from the left table, and NULLs for non-matching left table columns.

GGGGGGGG. Implementing Right Outer Join

Consider two tables: Suppliers and Orders.

📌 Example: Right Outer Join SQL Statement

SELECT supplier.sid, supplier.sname, orders.doe
FROM supplier, orders
WHERE supplier.sid(+) = orders.sid;

The (+) operator on the left side (supplier.sid) indicates a right outer join, meaning all rows from the right table (orders) are retained.

The result includes the row for order_id 500127 even if it has no matching supplier, with the supplier_name field containing a null value.

HHHHHHHH. Subquery Basics

A subquery is a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at run time. An SQL subquery is usually added in the WHERE Clause of the SQL statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement but do not know the exact value in the database. Subqueries are an alternate way of returning data from multiple tables, or simply, an alternative of joins.

🔑 Definition — Subquery: A nested query placed inside another SQL statement, used to dynamically determine criteria at runtime.

📐 Formula: General Syntax

SELECT *
FROM t1
WHERE column1 = (SELECT column1 FROM t2);

IIIIIIII. Implementing Subqueries

📌 Scenario 01: Write a query to display information of all those employees who are earning the minimum salary.

SELECT ename, sal, deptno
FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp);

📌 Scenario 02: Write a query to display all those deptno where the minimum salary is less than the average salary of all employees.

SELECT deptno, MIN(sal)
FROM employees
GROUP BY deptno
HAVING MIN(sal) < (SELECT AVG(sal) FROM employees);

⭐ Key Takeaways

For the exam, you must understand that a Cartesian product generates all possible row combinations and is only useful when filtered with a proper join condition. Inner joins combine tables using a common key and return only matching rows. Self-joins allow a table to be compared with itself, useful for recursive relationships like manager-employee hierarchies. Outer joins (left and right) extend inner joins by retaining all rows from one side, filling non-matching fields with NULL. Finally, subqueries provide a dynamic way to filter data when exact values are unknown, serving as an alternative to joins for multi-table queries.

🧠 Quick Revision Questions

1. What is the mathematical result of a Cartesian product between two sets of sizes m and n?
2. How does an inner join differ from a Cartesian product in terms of the rows it returns?
3. In what type of database relationship would you use a self-join, and what is the basic syntax?
4. What is the difference between a LEFT OUTER JOIN and a RIGHT OUTER JOIN regarding which table's rows are retained?
5. Write a SQL subquery to find employees whose salary is greater than the average salary of all employees.

📘 Lecture 20 — Database Views and Data Dictionaries in Oracle 11g

📖 Overview: This lecture introduces SQL views as virtual tables that derive data from base tables, and explains their syntax, creation, and practical applications. It also covers data dictionaries — the read-only system tables that store metadata about the database — and how to use them for database management and administration.

🗂️ Topics Covered

The lecture covers the motivation for using views, the syntax for creating views, writing basic and complex views including the WITH CHECK OPTION constraint, data dictionary concepts and their primary uses, retrieving information from data dictionary views like ALL_TABLES and USER_CONSTRAINTS, and the relationship between data dictionaries and views.

📝 Lecture Summary

A. Motivation for Views

A view can be thought of as either a window of a table or a virtual table. A view is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based; the view does not have its own data. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves. The SQL VIEW is, in essence, a virtual table that does not physically exist. Rather, it is created by a pre-defined SQL statement that joins one or more tables.

Access to data can be improved using SQL views as they summarize data from various tables which can further be used to generate reports. Views are very powerful because they allow you to tailor the presentation of data to different types of users. While views can help to obscure large queries from users and to standardize data access, a view can also draw data from several different tables and present it as a single table, turning multi-table queries into single-table queries against the view, and the queries can be reused to reduce time. All DML statements can be issued against a view.

💡 Why this matters: Views provide both security (by hiding sensitive columns) and simplicity (by pre-joining tables) — they are essential for building user-friendly database applications.

Syntax of a View

The following is the general syntax of a view:

🔑 Definition — View Creation Syntax:

CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;

Writing Views

Suppose we have EMP and DEPT tables. To see the empno, ename, sal, deptno, department name and location, we would normally have to give a join query like this:

CREATE VIEW emp_det AS
SELECT e.empno, e.ename, e.sal, e.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;

This SQL CREATE VIEW example creates a virtual table based on the result set of the SELECT statement. You can now query the SQL VIEW as follows:

SELECT * FROM emp_det;

This will show the same result as typing the long join query. Now you can treat this EMP_DET view the same as any other table.

Writing Complex Views

WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATE statements issued against the view are not allowed to create or result in rows that the view cannot select. Consider the following example:

Suppose all employees working in Department No. 10 belong to the accounts department and most of the time you deal with these people. So every time you have to give a DML or SELECT statement you have to give a WHERE condition like WHERE DEPTNO=10. To avoid this, you can create a view as given below:

CREATE VIEW accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 10
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;

Now to see the account people you don't have to give a query with where condition — you can just type:

SELECT * from accounts_staff;

The query that defines the ACCOUNTS_STAFF view references only rows in department 10. Considering the example above, the following INSERT statement successfully inserts a row into the EMP table through the ACCOUNTS_STAFF view:

INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10);

However, the following INSERT statement is rolled back and returns an error because it attempts to insert a row for department number 30, which could not be selected using the ACCOUNTS_STAFF view:

INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30);

🔑 Definition — WITH CHECK OPTION: Ensures that any DML operations (INSERT, UPDATE) performed through the view will not create rows that the view itself cannot select.

📌 Example: The accounts_staff view only shows department 10 employees. Inserting a row with Deptno=30 through this view fails because that row would not be visible in the view.

Data Dictionary Concepts

Data dictionary is a read-only set of tables that provides information about its associated database. A data dictionary contains the definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on). A dictionary, as said above, is built in tables which are created and maintained by the system.

The data dictionary has three primary uses:

• Oracle accesses the data dictionary to find information about users, schema objects, and storage structures.
• Oracle modifies the data dictionary every time that a data definition language (DDL) statement is issued.
• Any Oracle user can use the data dictionary as a read-only reference for information about the database.

Using Data Dictionary Concepts

Few data dictionary views are mentioned in the table below:

A complete list of data dictionary views is available online.

Data Dictionary & Views

The views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information. Most users are given access to the views rather than the base tables. All data dictionary information is stored in tables, but much of the data is presented to users through views. In other words, users don't get direct access to the tables of the data dictionary; they instead get access to the views, which provide somewhat limited access in order to protect the integrity of the data dictionary. The code of a view can be retrieved by using the data dictionary with the following syntax:

SELECT * FROM user_views;

⭐ Key Takeaways

Views are virtual tables that do not store data themselves but derive it from base tables using pre-defined SELECT statements. The WITH CHECK OPTION is critical for enforcing data integrity when performing DML operations through views — it prevents inserts or updates that would create rows invisible to the view. Data dictionaries are system-maintained read-only tables that store metadata about all database objects; they are automatically updated whenever DDL statements are executed. Users access data dictionary information through views (like ALL_TABLES and USER_CONSTRAINTS) rather than directly querying the underlying system tables, which protects the database's integrity. Understanding views and data dictionaries is essential for database administration, security implementation, and efficient query management.

🧠 Quick Revision Questions

1. What is a view and how does it differ from a base table in terms of data storage?
2. Explain the purpose of the WITH CHECK OPTION clause when creating a view. What happens if you try to insert a row through a view that violates this constraint?
3. List the three primary uses of a data dictionary in Oracle.
4. Why do users access data dictionary information through views rather than directly querying the system base tables?
5. Which data dictionary view would you query to find all constraint definitions on tables owned by the current user?

📘 Lecture 21 — Introduction to Sequence and Synonyms with implementation in Oracle 11g

📖 Overview: This lecture introduces sequences as a database feature for generating unique numeric values automatically, primarily for primary key columns. It covers the syntax for creating sequences, their implementation in Oracle 11g, interaction with the data dictionary, and their use with Data Manipulation Language (DML) commands. Understanding sequences is essential for maintaining data integrity in multi-user database environments.

🗂️ Topics Covered

The lecture covers the motivation for using sequences to guarantee unique primary key values, the full syntax of the CREATE SEQUENCE command with all parameters (MINVALUE, MAXVALUE, START WITH, INCREMENT BY, CACHE, CYCLE/NO CYCLE), implementation through a practical example creating the "customers_seq" sequence, retrieving sequence values using the dual table and Data Dictionary views, and applying sequences with DML commands like INSERT and DROP.

📝 Lecture Summary

A. Motivation for sequence

A sequence is a feature supported by some database systems to produce unique auto-number values on demand. Through sequences, unique numbers can be generated automatically to be used in tables. A sequence is a database object from which multiple users can generate unique integers. Using a sequence generator to provide the value for a primary key in a table is an easy way to guarantee that the key value is unique. Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables.

NNNNNNNN. Syntax of Sequence

The syntax of sequence is as follows:

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
CYCLE / No CYCLE;

Where:

• MINVALUE: Specify the minimum value of the sequence.
• MAXVALUE: Specify the maximum value the sequence can generate.
• START WITH: Specify the first sequence number to be generated.
• INCREMENT BY: Specify the interval between sequence numbers.
• CACHE: Specify how many values of the sequence the database pre-allocates and keeps in memory for faster access.
• CYCLE: Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.
• NO CYCLE: Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.

🔑 Definition — Sequence: A database object that generates unique integers automatically, independent of tables, allowing multiple users to generate unique values for primary keys.

OOOOOOOO. Implementing Sequence

The following statement creates the sequence customers_seq in the sample schema. This sequence could be used to provide customer ID numbers when rows are added to the customers table.

CREATE SEQUENCE customers_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;

📐 Formula: Sequence creation pattern → START WITH [initial_value] INCREMENT BY [step] [CACHE/NOCACHE] [CYCLE/NOCYCLE]

📌 Example: The customers_seq sequence starts at 1000, increments by 1, does not pre-cache values, and does not cycle. The first reference to customers_seq.nextval returns 1000. The second returns 1001. Each subsequent reference will return a value 1 greater than the previous reference.

PPPPPPPP. Sequence & Data Dictionary

The Data Dictionary stores information about sequences. To view sequence details, you can query the user_sequences view:

SELECT * FROM user_sequences
WHERE sequence_name = 'customers_seq';

This query retrieves all metadata about the "customers_seq" sequence from the Oracle Data Dictionary.

QQQQQQQQ. Sequence & DML

Data Manipulation Language (DML) commands can be used with sequences for inserting, deleting, or updating data in the sequence. Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables.

🔑 Definition — Dual table: A special dummy table in Oracle that is used when you need to select a value without referencing an actual table.

📌 Example: Consider an employee database in which a sequence is created with the name mySeq to auto-generate employee numbers:

• To retrieve the current and next values of a sequence without using Data Dictionary:

  SELECT customers_seq.nextval, customers_seq.currval FROM dual;
  

• To insert a row into the sequence:

  INSERT INTO emp (empno) VALUES (mySeq.nextVal);
  

• To drop (delete) the sequence:

  DROP SEQUENCE mySeq;
  

💡 Why this matters: Using NEXTVAL and CURRVAL is the primary way to access sequence values in queries and DML operations. The dual table provides a simple way to test sequence behavior without affecting actual table data.

⭐ Key Takeaways

A sequence is a vital database object for automatically generating unique primary key values, ensuring data integrity in multi-user environments where many users may be inserting records simultaneously. The CREATE SEQUENCE command requires specifying START WITH and INCREMENT BY, with optional CACHE for performance and CYCLE/NO CYCLE for reuse behavior. After creating a sequence, you retrieve its next value using NEXTVAL and the current value using CURRVAL, often through the dual table. Sequences are independent of tables, so the same sequence can serve multiple tables, and DML commands like INSERT and DROP work directly with sequences. Always check the Data Dictionary view user_sequences to verify sequence properties.

🧠 Quick Revision Questions

1. What is a sequence in Oracle and why is it useful for primary keys?
2. In the CREATE SEQUENCE syntax, what is the difference between CYCLE and NOCYCLE?
3. How do you retrieve the next value from a sequence named "emp_seq" without inserting into a table?
4. Can the same sequence be used to generate primary keys for multiple different tables? Explain.
5. What SQL command is used to remove a sequence from the database, and what is its syntax?

📘 Lecture 22 — Indexes in Databases

📖 Overview: This lecture introduces indexes as database objects that improve the speed of data retrieval. It explains how indexes work by sorting values and storing row addresses, enabling the database to locate records without scanning every row. The lecture covers index creation, structure, and how to view index information in the data dictionary, which is critical for optimizing query performance.

🗂️ Topics Covered

The lecture begins with an explanation of the basic concept of indexes as sorted data structures that speed up record lookup, illustrated with a table and index table example. It then details how indexes work by creating a separate structure with key values and row IDs, sorted alphabetically. The syntax for creating indexes in Oracle using the CREATE INDEX statement is provided with examples. Finally, the lecture discusses indexes in the data dictionary, explaining how to query USER_INDEXES and USER_IND_COLUMNS to view index metadata.

📝 Lecture Summary

A. Index Basics

An index is a data structure that the database uses to find records within a table more quickly. Indexes are built on one or more columns of a table; each index maintains a list of values within that field that are sorted in ascending or descending order. Rather than sorting records on the field or fields during query execution, the system can simply access the rows in order of the index. In simple terms, an index is a database object that helps in efficient searching of the data.

Every time a database table is accessed, all the rows in the table are searched to find the required data, which limits database performance. Indexes are used to quickly locate data without having to search every row in a database table every time it is accessed. For example, to search the salary of "Aqeel," all rows in the table would be scanned to locate that record. In large tables, this can tremendously increase the run time of a query.

💡 Why this matters: Without indexes, even simple lookups require full table scans, which become prohibitively slow as tables grow.

B. Searching Using Index

The index is created on one or more columns and makes it easier to locate records in a table. The index creates another table or index structure, which is not a part of the actual table. This index structure acts as a supplement to the table and contains just two columns: the key values and a row ID/address to the actual row in the table.

The index table/structure sorts the data of the column in alphabetical order, with the address or row ID in another column. As shown in the lecture example, after the index table is created, the data has been sorted alphabetically and the row ID for each record is stored. Now to search any record, the index table can provide the row ID for each record, allowing direct access to the required row without scanning the entire table.

C. Creating Index in Oracle

Creating an index involves the CREATE INDEX statement, which allows you to name the index, specify the table, and specify which column or columns to index. The basic syntax is as follows:

CREATE INDEX index_name
ON table_name (column_name);

Examples:

CREATE INDEX test_emp
ON emp (ename);

CREATE INDEX emp_ind
ON emp (job, sal);

🔑 Definition — Index: A database object that stores sorted key values along with row addresses, enabling efficient data retrieval without full table scans.

📐 Formula (Syntax): CREATE INDEX index_name ON table_name (column_name); → This creates a sorted index on the specified column(s) of the given table, which the database will use to speed up queries that search or sort by those columns.

D. Indexes and Data Dictionary

An index is a database object, and the database maintains a Data Dictionary for it. To view information about indexes, the following commands can be used:

• SELECT * FROM user_indexes; — This displays all the indexes created by the currently logged-in user schema.

• SELECT * FROM user_IND_COLUMNS; — This displays all the columns of indexes created by the currently logged-in user schema.

These data dictionary views allow database developers and administrators to inspect which indexes exist, on which tables, and on which columns, helping them understand and manage the indexing structure of the database.

⭐ Key Takeaways

An index is a crucial database object that stores sorted key values with row IDs, enabling the database to find records without scanning every row. Every time a table is accessed, without an index, all rows must be searched, which severely limits performance on large tables. The CREATE INDEX statement specifies the index name, table, and column(s) to index, as shown with examples on the emp table. Index metadata is maintained in the data dictionary, viewable via user_indexes (for index names and details) and user_ind_columns (for the specific columns indexed). For the exam, remember that an index is a separate structure—not part of the actual table—that acts as a supplement for fast lookups.

🧠 Quick Revision Questions

1. What is an index, and how does it improve query performance?
2. What two pieces of information are stored in an index structure?
3. Write the SQL command to create an index named emp_idx on the salary column of the employee table.
4. Which data dictionary view displays all indexes created by the currently logged-in user, and which view shows the columns in those indexes?
5. Why would searching for a record like "Aqeel" be slow without an index, and how does the index speed up that search?