DBMS EssentialsKey Concepts, MCQs & Revision Notes

Module 1 – DBMS Fundamentals

DBMS Fundamentals

• A database is an organized collection of related data.
• A DBMS is software used to create, store, retrieve, update, secure, and manage data.
• Examples of DBMS include:
• MySQL.
• Oracle Database.
• PostgreSQL.
• Microsoft SQL Server.
• MongoDB.
• Redis.
• A DBMS provides an interface between users/applications and stored data.
• Major functions of a DBMS include:
• Data storage.
• Data retrieval.
• Data insertion.
• Data modification.
• Data deletion.
• Security.
• Backup and recovery.
• Concurrency control.
• Transaction management.
• A file-processing system stores data in separate application-specific files.
• A major disadvantage of traditional file systems is data redundancy.
• Data redundancy means storing the same data repeatedly.
• Redundancy can cause:
• Wasted storage.
• Inconsistent values.
• Update anomalies.
• Insert anomalies.
• Delete anomalies.
• A DBMS reduces uncontrolled redundancy by centralizing data management.
• Data inconsistency occurs when the same data has different values in different places.
• Data integrity means maintaing the accuracy and consistency of data.
• Data security protects data from unauthorized access or modification.
• Data availability means that authorized users can access data when required.
• Data independence means changing one level of the database without requiring changes at another level.
• Logical data independence allows changes to the logical schema without changing external views or applications.
• Physical data independence allows changes to physical storage without changing the logical schema.
• A database schema describes the structure of the database.
• A database instance is the actual data stored in the database at a particular time.
• The schema changes rarely, while the database instance changes frequently.
• A data model defines how data, relationships, constraints, and operations are represented.
• Common data models include:
• Relational.
• Hierarchical.
• Network.
• Object-oriented.
• Document.
• Graph.
• Key-value.
• A relational DBMS, or RDBMS, stores data in tables.
• An RDBMS represents relationships using keys and constraints.
• SQL is the standard language used to interact with relational databases.
• RDBMS examples include MySQL, Oracle, SQL Server, and PostgreSQL.
• A database administrator, or DBA, manages database security, performance, backup, and recovery.
• A DBMS may support multiple users accessing the same data concurrently.
• Concurrency control prevents simultaneous operations from producing inconsistent results.
• A DBMS uses transactions to maintain reliable database operations.

Relational Database Concepts

• A relational database stores information in relations, commonly represented as tables.
• A table contains rows and columns.
• A row represents one record or tuple.
• A column represents an attribute or field.
• A relation is a table in the relational model.
• A tuple is a row in a relation.
• An attribute is a named column of a relation.
• A domain is the set of permitted values for an attribute.
• A relation should not contain duplicate tuples in the pure relational model.
• The order of rows in a relational table is not logically significant.
• The order of columns is also not logically significant, although SQL displays them in a selected order.
• A relational schema defines the relation name, attributes, and constraints.
• A primary key uniquely identifies each row in a table.
• A primary key cannot contain NULL.
• A table can have only one primary-key constraint, although the key may contain multiple columns.
• A composite primary key contains more than one column.
• A candidate key is a minimal set of attributes that uniquely identifies a row.
• A table can have multiple candidate keys.
• One candidate key is selected as the primary key.
• A superkey is any set of attributes that uniquely identifies a row.
• Every candidate key is a superkey.
• Every superkey is not necessarily a candidate key because it may contain unnecessary attributes.
• A foreign key is an attribute that refers to a key in another table.
• A foreign key establishes a relationship between tables.
• A foreign key generally references a primary key or a candidate key.
• A foreign key may contain NULL if the column is not declared NOT NULL.
• Referential integrity ensures that a foreign-key value refers to a valid referenced row or is NULL.
• A unique key ensures that values in a column or column group are unique.
• A table may have multiple unique constraints.
• The treatment of NULL in a unique column can vary by DBMS.
• A natural key is based on meaningful real-world data, such as an email or national ID.
• A surrogate key is an artificial identifier, such as an auto-increment integer.
• A composite key is useful when no single attribute uniquely identifies a record.
• A relationship describes an association between enties.
• A one-to-one relationship maps one entity to one entity.
• A one-to-many relationship maps one entity to multiple enties.
• A many-to-many relationship maps multiple enties on both sides.
• A many-to-many relationship is commonly implemented using an associative or junction table.
• A weak entity depends on another entity for identification.
• An entity is a distinguishable real-world object.
• An attribute describes a property of an entity.
• A simple attribute cannot be divided into smaller meaningful parts.
• A composite attribute can be divided into subattributes.
• A derived attribute is calculated from other attributes.
• A multivalued attribute can have multiple values for one entity.

ER Model and Database Design

• ER stands for Entity-Relationship.
• An ER diagram visually represents enties, attributes, and relationships.
• Enties are commonly represented by rectangles.
• Attributes are commonly represented by ellipses.
• Relationships are commonly represented by diamonds.
• A primary-key attribute is often underlined in an ER diagram.
• ER modeling helps identify data requirements before creating tables.
• Database design generally involves:
• Requirement analysis.
• Entity identification.
• Attribute identification.
• Relationship identification.
• Constraint definition.
• Schema conversion.
• Normalization.
• Performance planning.
• An ER diagram can be converted into a relational schema.
• A strong entity generally becomes a table.
• A strong entity’s attributes become table columns.
• A strong entity’s key becomes the table’s primary key.
• A one-to-many relationship is usually represented by placing the primary key of the “one” table as a foreign key in the “many” table.
• A many-to-many relationship is represented using a separate junction table.
• The junction table usually contains foreign keys referencing both participating tables.
• Cardinality specifies how many instances of one entity can be related to another.
• Common cardinalities include:
• One-to-one.
• One-to-many.
• Many-to-many.
• Participation specifies whether participation in a relationship is mandatory or optional.
• Total participation means every entity must participate.
• Partial participation means participation is optional for some enties.
• A good database design reduces redundancy and preserves integrity.
• Over-normalization may increase the number of joins required.
• Denormalization may improve read performance but can introduce redundancy.
• A database constraint is a rule applied to data.
• Common constraints include:
• PRIMARY KEY.
• FOREIGN KEY.
• UNIQUE.
• NOT NULL.
• CHECK.
• DEFAULT.