Computer Science and Information Technology

Trishna Knowledge Systems

ISBN #9789352868469

2018 Edition

1,832 Questions

27,660 Students Helped

Homework Questions

Summary

Learning Objectives

Key Concepts

Example Problems

Explanations

Common Mistakes

Summary

This section introduces the fundamental concepts of databases and DBMS. It explains how databases serve as organized collections of related data and elaborates on the multifaceted roles of a DBMS in defining, constructing, manipulating, and sharing these databases. The discussion also distinguishes between the static database schema, representing the design and structure (intension), and the dynamic database state, representing the actual data (extension). Additionally, the concept of data models is introduced as a means to achieve data abstraction and effectively manage database systems.

Learning Objectives

Define a database and understand its components including data and implicit meaning.

Explain the role and functions of a Database Management System (DBMS) in database lifecycle activities.

Describe the concepts of data models, database schemas, and the distinction between schema (intension) and state (extension).

Understand how data is abstracted and managed through DBMS processes such as defining, constructing, manipulating, and sharing.

Key Concepts

CONCEPT

DEFINITION

Database

A collection of related data that possesses implicit meaning, such as records like names, telephone numbers, and addresses.

Database Management System (DBMS)

A collection of programs that allows users to create, maintain, manipulate, and share databases by handling tasks such as defining schemas, storing data, processing queries, and enabling concurrent access.

Data Model

A set of concepts used to describe the structure of a database and provide a means to achieve data abstraction.

Database Schema

The description of the database structure, including data types, structures, and constraints; often referred to as the intension.

Database State

The actual data present in the database at a specific point in time, also known as the extension.

Metadata

The stored descriptions of schema constructs and constraints within the DBMS catalog, which help in managing the underlying database structure.

Example Problems

Example 1

Consider the following two tables $\mathrm{T}_{1}$ and $\mathrm{T}_{2}$. Show the output for the following operations: What is the number of tuples present in the result of given algebraic expressions? (i) $T_{1} \bowtie_{T 1 . P}={ }_{T 2 A} T_{2}$ (A) 2 (B) 3 (C) 4 (D) 5 (ii) $T_{1} \bowtie_{\pi 1 . Q}={ }_{72 . B} T 2$ (A) 2 (B) 3 (C) 4 (D) $.5$ (iii) $T_{1} \bowtie_{(T, p=T 2 . A \text { AND } \cap . R=T 2 . C)} \mathrm{T}_{2}$ (A) 1 (B) 2 (C) 3 (D) $.4$

Example 2

Suppose $R_{1}(A, B)$ and $R_{2}(C, D)$ are two relation schemas. Let $R_{1}$ and $R_{2}$ be the corresponding relation instances. $B$ is a foreign key that refers to $C$ in $R_{2}$. If data in $R_{1}$ and $R_{2}$ satisfy referential integrity constraints, which of the following is true? (A) $\pi_{B}\left(R_{1}\right)-\pi_{c}\left(R_{2}\right)=\phi$ (B) $\pi_{C}\left(R_{2}\right)-\pi_{B}\left(R_{1}\right)=\phi$ (C) $\pi_{B}\left(R_{1}\right)-\pi_{C}\left(R_{2}\right) \neq \phi$ (D) Both $A$ and $B$

Example 3

Consider the following relations: How many tuples does the result of the following relational algebra expression contain? Assume that the scheme of $(A \cup B)$ is the same as that of $A$. $(A \cup B) \bowtie_{A . A>40 v c I d<15} C$ (A) 6 (B) 7 (C) 8 (D) 9$A, B$ and $C$

Example 4

Consider the relations $A, B$ and $C$ given in Question $3 .$ How many tuples does the result of the following SQL query contain? $\begin{array}{ll}\text { SELECT } & \text { A.Id }\end{array}$ FROM $\quad \mathrm{A}$ WHERE $\quad$ A.Age> $\begin{array}{ll}\text { ALL } & \text { (SELECT B.Age }\end{array}$ FROM $\quad \mathrm{B}$ $\begin{array}{ll}\text { WHERE } & \text { B.Name }=\text { 'Arun' } \text { )? }\end{array}$ (A) 0 (B) 1 (C) 2 (D) 3

Example 5

Consider a database table $T$ containing two columns $X$ and $Y$ each of type integer. After the creation of the table, one record $(X=1, Y=1)$ is inserted in the table. Let MX and MY denote the respective maximum value of $X$ and $Y$ among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with $X$ and $Y$ values being $\mathrm{MX}+1$, $2 * \mathrm{MY}+1$, respectively. It may be noted that each time after the insertion, values of $\mathrm{MX}$ and $\mathrm{MY}$ change. What will be the output of the following SQL query after the steps mentioned above are carried out? SELECT $Y$ FROM $T$ WHERE $X=7 ; ?$ (A) 15 (B) 31 (C) 63 (D) 127

Step-by-Step Explanations

QUESTION

How do you define a database and what are its core components?

STEP-BY-STEP ANSWER:

Step 1: Recognize that data are facts that have implicit meaning.
Step 2: Understand that related pieces of such data can be collected and organized together.
Step 3: Consider an example like names, telephone numbers, and addresses being organized in an address book.
Final Answer: A database is a structured collection of related data that has implicit meaning.

Database

QUESTION

What are the key functions provided by a Database Management System (DBMS)?

STEP-BY-STEP ANSWER:

Step 1: Define the structure of the database by specifying data types, structures, and constraints.
Step 2: Construct the database by storing the data on a controlled storage medium.
Step 3: Manipulate the database by querying and updating data to reflect changes.
Step 4: Share the database among multiple users and applications concurrently.
Final Answer: A DBMS is a software system that facilitates the defining, constructing, manipulating, and sharing of databases.

DBMS

QUESTION

How do you distinguish between a database schema and a database state?

STEP-BY-STEP ANSWER:

Step 1: Understand that the database schema is the structural description created during design and stored as metadata.
Step 2: Recognize that the database state is the actual instance of data which changes over time.
Step 3: Observe that while the schema (intension) remains relatively static, the state (extension) is dynamic.
Final Answer: The database schema is the defined framework of the database, whereas the database state is the current set of data in that framework.

Schema vs. State

Common Mistakes

Confusing raw data with meaningful information.
Mixing up the roles of the database and the DBMS.
Failing to distinguish between the database schema (structure) and the database state (actual data).
Overlooking the importance of data abstraction provided by data models.
Assuming that the schema changes as frequently as the data state.