Learning Objectives – after this chapter you will be able to:
- Explain what a DBMS is and list its main features.
- Compare the hierarchical, network and relational data models.
- Use the terms table, row (tuple), column (attribute), primary key, composite key and foreign key.
- State the domain, entity and referential integrity rules.
- Describe the relational algebra operators and the four categories of SQL commands.
- Differentiate centralized, decentralized and distributed (client/server) data processing.
What is a DBMS?
Data is one of the most valuable assets of an organisation, so it must be stored accurately and retrieved quickly. A Database Management System (DBMS) is a set of programs used to store and manipulate data. Manipulating data means:
- Adding new data – e.g. the details of a newly admitted student.
- Deleting unwanted data – e.g. students who have completed the course.
- Changing existing data – e.g. the fee paid by a student.
Every DBMS (Oracle, DB2, Sybase, MS-Access …) is meant for data management, but they do not all offer the same set of functions. Powerful systems such as Oracle and DB2 suit large organisations, while MS-Access suits small ones – the DBMS is chosen according to the requirement.
Features of a DBMS
| Feature | What it means |
|---|---|
| Support for large amounts of data | Special ways to store and manipulate very large data. The limit is usually the available hardware, not the DBMS (Oracle can store terabytes of data). |
| Data sharing, concurrency and locking | Many users can access the same data at the same time (concurrency). To stop two users from modifying the same data at the same moment, the DBMS locks the data being changed. Locking is automatic and transparent. |
| Data security | Only authorised users can access data. By default a user’s data cannot be accessed by others unless the owner gives explicit permission. |
| Data integrity | Rules can be declared once and the DBMS enforces them always. Domain, entity and referential integrity are always supported. |
| Fault tolerance and recovery | The DBMS keeps running in spite of errors where possible, and data can be recovered to the point of failure (e.g. after a disk crash) if a proper backup exists. |
| Support for languages | A non-procedural language – SQL (compliant with ANSI standards) – plus a procedural language: Oracle provides PL/SQL and SQL Server provides T-SQL. |
Entity, Attribute and Relationships
- An entity is any object about which data is stored – e.g. a student in a training institute.
- Each entity has attributes – the values that describe it, e.g. roll number, name and course.
- Entities are related to each other. With entities student, batch and subject:
- One-to-one – one batch is associated with only one subject.
- One-to-many – one batch contains many students.
- Many-to-many – a student takes many subjects and a subject is taken by many students.
Data Models
A data model is a way of storing and retrieving data. The models differ in how they let users view and manipulate the relationships between entities.
| Model | How data is stored | Relationships | Example product |
|---|---|---|---|
| Hierarchical | As a tree (parent–child); each tree has one root record and subordinate records – e.g. batch as root, its students as subordinates. | Only one-to-many | IBM’s Information Management System (IMS) |
| Network | Data stored with pointers that specify relationships. Complex to understand and manipulate. | Supports many-to-many | Honeywell’s Integrated Data Store (IDS) |
| Relational | As tables (rows and columns). | Dynamic – built from column values | Oracle, DB2, SQL Server … |
Relational Database Management System (RDBMS)
A DBMS based on the relational model is an RDBMS. The relational model was designed by E. F. Codd and is based on the mathematical theory of sets and relations. It is the most successful of the three models because:
- It is simple – data is a two-dimensional table that is easy to understand and manipulate.
- It does not bind data to the relationships between data items; relationships are formed dynamically using column values.
Terms used in the relational model
| Term | Meaning |
|---|---|
| Table / Relation | A two-dimensional arrangement of rows and columns. Its table name depicts its content (e.g. STUDENTS). |
| Tuple / Row | A single row; holds the data of one entity (one student). |
| Attribute / Column | Holds one attribute of the entity (e.g. student name). Each column has a column name. |
| Primary key | A set of columns used to uniquely identify rows – e.g. ROLLNO in STUDENTS, account number, product code, employee number. |
| Composite primary key | A primary key made of two or more columns – e.g. ROLLNO + DP (date of payment) in PAYMENTS. |
| Foreign key | A column used to join a child table with its parent table; it is on the many side of a one-to-many relationship – e.g. ROLLNO of PAYMENTS refers to ROLLNO of STUDENTS. |
| Orphan record | A child row whose foreign key does not refer to any parent key – the result of lost data integrity. |
Sample tables used in the chapter
STUDENTS PAYMENTS ROLLNO NAME PHONE ROLLNO DP AMOUNT s1 Louis Figo 454333 s1 12-may-2001 1000 s2 Raul 656675 s2 12-may-2001 2500 s3 Roberto Carlos 546782 s1 23-may-2001 1500 s4 Guti 567345 s2 23-may-2001 1000 STUDENTS.ROLLNO -> primary key PAYMENTS.(ROLLNO, DP) -> composite primary key PAYMENTS.ROLLNO -> foreign key referring to STUDENTS.ROLLNO
Integrity Rules
If data loses integrity it becomes garbage, so integrity must be maintained at any cost.
- Domain integrity – every value of a column must come from its domain (the collection of potential values). A date-of-joining column must hold a valid date.
- Entity integrity – every table must have a primary key, and primary key values must be NOT NULL and UNIQUE.
- Referential integrity – a foreign key must be either NULL or hold a value that exists in the corresponding parent key. (No student who is not in STUDENTS can join a batch.)
Relational Algebra
Relational algebra is a set of operators that take one or more tables as input and always produce one table as the result.
| Operator | Input | Result | SQL (chapter) |
|---|---|---|---|
| Union | 2 tables | Rows in the first or second table (or both) | UNION (Ch-9) |
| Intersect | 2 tables | Rows present in both tables | INTERSECT (Ch-9) |
| Difference / Minus | 2 tables | Rows in the first table but not in the second | MINUS (Ch-9) |
| Project | 1 table | Vertical subset – selected columns | column list of SELECT (Ch-3) |
| Select | 1 table | Horizontal subset – rows that satisfy a condition | WHERE clause (Ch-3) |
| Join | 2 tables | Rows combined on a common column | Joins (Ch-9) |
Structured Query Language (SQL)
SQL is the standard language of relational databases. It is non-procedural: you state what you want, not how to get it. SQL commands fall into four categories:
- DDL (Data Definition Language) – defines data, e.g.
CREATE TABLE. - DML (Data Manipulation Language) – manipulates data, e.g.
INSERT,DELETE. - DCL (Data Control Language) – controls access to data, e.g.
GRANT. - Query – retrieves data with
SELECT.
DML and Query are collectively also called DML, and DDL and DCL together are called DDL.
Data Processing Methods
- Centralized – all data is stored and processed in one place, e.g. a mainframe with dumb terminals that only take input and show output.
- Decentralized – each department has its own system and data. Common data is duplicated (redundancy), which causes inconsistency – departments’ data stop agreeing.
- Distributed (client/server) – processing is shared: the server (a DBMS such as Oracle) manages data, the client (a front-end such as Visual Basic or Delphi) interacts with the user. Commands travel from client to server in SQL.
- 2-tier – client and database server.
- 3-tier – client ↔ application server ↔ database server; the application server validates and processes requests, taking load off both client and database.
- n-tier – web applications: browser → web server (ASP/JSP page) → application server → database server.
Key Takeaways
- A DBMS stores and manipulates data; an RDBMS is a DBMS based on Codd’s relational model.
- Primary keys identify rows uniquely; foreign keys join child tables to parent tables.
- Domain, entity and referential integrity keep data usable.
- Relational algebra operators always return a table; SQL implements them.
Chapter 1 Notes (PDF)
ch1.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 1 notes (PDF). Click a question to see the answer.
Answer : A DBMS (Database Management System) is a set of programs used to store and manipulate data. Manipulation of data includes adding new data (e.g. details of a new student), deleting unwanted data (e.g. students who completed the course) and changing existing data (e.g. modifying the fee paid by a student).
Answer : Support for large amounts of data; data sharing, concurrency and locking; data security; data integrity; fault tolerance and recovery; and support for languages (a non-procedural language such as SQL and a procedural language such as PL/SQL or T-SQL).
Answer : When the same data is manipulated by several users at the same time problems arise, so the DBMS locks data that is being modified to stop two users from modifying the same data at the same time. Locking is transparent and automatic – the programmer does not have to inform the DBMS or know when it locks, although understanding the mechanism makes one a better programmer.
Answer : While allowing data to be shared, the DBMS ensures data is accessed only by authorised users. By default the data of a user cannot be accessed by other users unless the owner gives them explicit permission.
Answer : Fault tolerance means the DBMS continues to run in spite of errors, where possible, allowing users to rectify mistakes in the meantime. Recovery means that even if data on disk is completely lost (e.g. disk failure), it can be recovered to the point of failure provided a proper backup is available.
Answer : An entity is any object whose data is stored in the database, e.g. a student of a training institute. An attribute is a value associated with the entity, e.g. the student’s roll number, name and course.
Answer : One-to-one: one batch is associated with only one subject. One-to-many: one batch contains many students. Many-to-many: a student may take many subjects and a subject may be taken by many students.
Answer : The hierarchical model stores data as a tree with a parent–child relationship (one root and subordinate records) and supports only one-to-many relationships; it was used in IBM’s IMS. The network model stores data along with pointers that specify relationships, can support many-to-many relationships, but is complex to understand and manipulate; it was used in Honeywell’s IDS.
Answer : Because of its simplicity – data is represented as tables of rows and columns that are easy to understand and manipulate – and because it does not bind data with the relationships between data items; relationships are formed dynamically using the values of columns.
Answer : A primary key is a set of columns used to uniquely identify rows of a table, e.g. ROLLNO in STUDENTS. When no single column can identify rows and two or more columns are combined, it is called a composite primary key, e.g. ROLLNO and DP (date of payment) in the PAYMENTS table.
Answer : A foreign key is a column used to join a child table with its parent table, e.g. ROLLNO of PAYMENTS refers to ROLLNO of STUDENTS. It is on the many side of a one-to-many relationship, and its values must be derived from the parent key.
Answer : An orphan record is a row in a child table that does not refer to any corresponding parent key. Orphan records must not exist because they are the result of a lack of data integrity.
Answer : Domain integrity: the value of a column must be derived from its domain (the collection of potential values, e.g. all valid dates). Entity integrity: every table must have a primary key whose values are not null and unique. Referential integrity: a foreign key must be either null or have a value derived from the corresponding parent key.
Answer : Relational algebra is a set of operators (union, intersect, difference/minus, project, select and join) that take one or more tables and produce one table as the result. Project takes a single table and returns a vertical subset (selected columns); Select takes a single table and returns a horizontal subset (only rows satisfying a condition).
Answer : DDL defines data (CREATE TABLE), DML manipulates data (INSERT, DELETE), DCL controls access (GRANT) and Query retrieves data (SELECT). In 2-tier client/server there are only a client and a database server; in 3-tier an application server sits between them, processing and validating client requests and passing them to the database server, taking some burden off both.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM, 13(6), 377–387. https://doi.org/10.1145/362384.362685
- Oracle Corporation. (n.d.). Oracle Database Concepts, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/
- Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems (7th ed.). Pearson.
- Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill Education.
- Date, C. J. (2003). An Introduction to Database Systems (8th ed.). Addison-Wesley.
- International Organization for Standardization / International Electrotechnical Commission. ISO/IEC 9075 – Information technology – Database languages – SQL (multi-part standard).