Introduction to Database Management System: One Mark Questions, Book Back Answers and Notes

About Introduction to Database Management System

Medium ~90 min study

A school office keeps records of students, marks and fees. Once these records grow, storing them in separate files becomes a problem: the same data is repeated in many files, keeping the copies correct takes effort, and anyone who can open a file can read it. A Database Management System (DBMS) solves these problems by storing related data in one place and controlling who can see or change it.

The chapter first traces how data storage moved from paper records to punched cards, then to computer files, and finally to databases. It then describes the database models: hierarchical, network, relational and object-oriented. Most of the chapter concentrates on the relational model, because MySQL, the database used with PHP later in the book, is relational.

In exams, expect questions on the ACID properties, the meaning of primary and foreign keys, the symbols used in an ER diagram, and which SQL command belongs to DDL, DML, DQL, TCL or DCL. These topics are also what you will use when you connect a PHP page to a MySQL database in the practical classes.

What you'll learn

• Describe the hierarchical, network, relational and object-oriented database models.
• Explain the relational terms table, tuple, attribute and schema.
• State and explain each of the ACID properties with an example.
• Draw a simple ER diagram using the correct symbols and cardinality.
• Name tools used to manage MySQL, such as phpMyAdmin, MySQL Workbench and HeidiSQL.
• Classify SQL commands into DDL, DML, DQL, TCL and DCL and write simple queries.

Before you start

• General computer terms such as file, folder and software.
• Experience with data arranged in rows and columns, for example in a spreadsheet.
• A basic idea of how a web browser requests pages from a server.

Introduction to Database Management System explained

Database Management System: Models, Keys, ER Diagrams and SQL

From file systems to DBMS

Data was first kept in manual registers, then on punched cards, then in computer files. File systems had clear weaknesses: data redundancy (the same data stored in several files, wasting space), high maintenance effort to keep the copies consistent, and poor security. A database is an organised collection of related data. A DBMS is the system software used to create, retrieve, update and manage that data safely. MySQL, Oracle and SQL Server are examples.

Database models

A database model describes how data is organised and linked. The hierarchical model arranges records in a tree, where each child record has only one parent. This makes many-to-many relationships hard to represent. The network model allows a child record to have more than one parent (owner), so it handles many-to-many relationships, but it is complex to design and maintain. The relational model stores data in tables made of rows and columns, and it is the model used by most databases today. The object-oriented model stores data as objects, bringing ideas from object-oriented programming into the database.

Relational terms and ACID properties

In a relational database a table is called a relation, a column is an attribute, and a row is a tuple or record. The design of the tables is called the schema. A primary key uniquely identifies each row, such as a register number. A foreign key is a primary key of one table placed in another table to link the two. A composite key uses two or more attributes together as the key. Every transaction must follow the ACID properties. Atomicity: all operations of a transaction are completed, or none are. Consistency: a transaction takes the database from one valid state to another valid state. Isolation: transactions running at the same time do not interfere with one another. Durability: once a transaction is committed, its changes are saved even if the system fails.

Entity-Relationship (ER) model

An ER diagram shows the design of a database before the tables are created. An entity is a real-world object such as a student or a book. Attributes describe an entity and may be key, simple, composite, single-valued or multi-valued. A student's register number is a key attribute; a phone number field that can hold several numbers is multi-valued. Entities are linked by relationships, whose cardinality may be one-to-one, one-to-many, many-to-one or many-to-many. In the diagram, an entity is drawn as a rectangle, an attribute as an ellipse and a relationship as a diamond (rhombus).

SQL and database administration

SQL (Structured Query Language) is the standard language for working with relational databases. Its commands fall into five groups. DDL (Data Definition Language) defines structure: CREATE, ALTER, DROP. DML (Data Manipulation Language) changes data: INSERT, UPDATE, DELETE. DQL (Data Query Language) retrieves data with SELECT. TCL (Transaction Control Language) manages transactions: COMMIT, ROLLBACK, SAVEPOINT. DCL (Data Control Language) controls access: GRANT, REVOKE. A database administrator installs the server, creates user accounts, assigns privileges, takes backups and tunes performance. Tools such as phpMyAdmin, MySQL Workbench and HeidiSQL give a graphical way to do this. After adding a user directly to the grant tables, the command FLUSH PRIVILEGES reloads those tables so the new rights take effect.

Common mistakes to avoid

• Treating SQL as the database software. SQL is a language; MySQL is the DBMS that understands it.
• Mixing up primary and foreign keys. A primary key identifies rows in its own table; a foreign key refers to the primary key of another table.
• Placing SELECT under DML. The textbook puts SELECT in DQL and keeps DML for INSERT, UPDATE and DELETE.
• Confusing single-valued and multi-valued attributes. A date of birth has one value; phone numbers can have several, so they are multi-valued.
• Drawing the wrong ER symbol. Entities are rectangles, attributes are ellipses and relationships are diamonds.
• Forgetting FLUSH PRIVILEGES after inserting a user directly into the grant tables, so the new permissions do not work yet.

Test yourself on these with the practice test, then check the worked reasoning in the solved MCQs.

Frequently asked questions

What is the difference between a database and a DBMS?

A database is the organised collection of related data itself, such as all the student records of a school. A DBMS is the software used to work with that data: to create tables, add, change and delete records, search them and control who has access.

What do the ACID properties mean?

Atomicity means a transaction is done completely or not at all. Consistency means it moves the database from one valid state to another. Isolation means transactions running together do not affect each other. Durability means committed changes stay saved even after a power or system failure.

What is the difference between a primary key and a foreign key?

A primary key is an attribute whose value is unique for every row, so it identifies each record. A foreign key is an attribute in one table that holds the primary key values of another table, and it is used to link the two tables.

How do the hierarchical and network models differ?

The hierarchical model arranges records like a family tree, and each child record has exactly one parent, so many-to-many relationships are hard to show. The network model allows a child record to have more than one parent, so many-to-many relationships can be represented.

What are the five groups of SQL commands?

DDL defines structure (CREATE, ALTER, DROP). DML changes data (INSERT, UPDATE, DELETE). DQL retrieves data (SELECT). TCL manages transactions (COMMIT, ROLLBACK, SAVEPOINT). DCL controls access rights (GRANT, REVOKE).

What is an ER diagram used for?

An ER diagram shows the plan of a database before it is built. Entities are drawn as rectangles, attributes as ellipses and relationships as diamonds. It helps the designer see which data is needed and how the entities are related, including cardinality such as one-to-many.

What does FLUSH PRIVILEGES do in MySQL?

FLUSH PRIVILEGES tells the MySQL server to reload the grant tables. If you add a user or change privileges by inserting rows directly into those tables, the change is not used until the tables are reloaded, so you run this command straight after.