COMPUTER SCIENCE 9TH - Short / Detailed Question Answers
INTRODUCTION TO DATABASE SYSTEM
Q.1: Define term database system?
Ans: Database:
A database stores data in organized form. Generally, a database is an electronic system that facilitates easy access, manipulation, and updating of data. A database is composed of tables which contain rows and columns. These rows and columns are called records and fields respectively. Most databases contain multiple tables.
For example:
A general store database may include tables for purchase, sales, and stock records. Each of these tables has different fields that are relevant to the information stored in the table.
Q.2: Define terms records and fields?
Ans: A database is composed of tables which contain rows and columns. These rows and columns are called records and fields respectively.
Q.3: Describe uses and advantages of database.
Ans: Use of Database:
Nowadays, everyone is familiar with the term database; it is a mathematically developed data structure which converts raw input data into meaningful information for a particular organization. These days, databases can be seen in every field of life, for example in industries, health, agriculture, schooling, business, and banking. The databases can be developed according to the size of its records for a particular organization.
Databases can be small in size with a few records or very large like NADRA (National Database Registration Authority) databases which keep the multi millions of records.
Advantages of Database:
A database is playing a leading role to enhance the efficiency and performance of any organization. The goal of a database is to minimize the loss and increase the productivity and efficiency of any organization in the age of information technology.
Q.4: Discuss the use of database in business with example.
Ans: Use of Database in Business:
Businesses use their databases to:
- Keep track of basic transactions
- Provide information that will help the company run the business more efficiently
- Help managers and employees make better decisions
Businesses run on databases. These repositories of organized information can store virtually every kind of data imaginable, and they can sort that information and deliver it to us with a click of a mouse. Business databases help business owners organize and track their customers, inventory, and employees.
Example - Customer Relationship Management:
A customer relationship management (CRM) database can help a small business manage the lifeblood of its business – its customers. A CRM database organizes all the information a company has about its accounts, contacts, leads, and opportunities. A single customer's record may include his contact details, the date and amount of his last order, the total amount of his purchases for the last year, a list of his favorite products and the products he returned, details of customer service calls, and more. Databases can also be used to manage marketing and promotions, to export email addresses, and to prepare shipping labels.
Q.5: Describe database management system and name some popular DBMs.
Ans: Database Management System (DBMS):
Databases are usually developed, maintained, and controlled by the Database Management System (DBMS). The DBMS essentially serves as an interface between databases and end users or application programs, ensuring that data is consistently organized and remains easily accessible.
Examples:
Here are some examples of popular DBMSs used these days:
- MySQL
- Oracle
- Microsoft SQL Server
- MongoDB
- Visual FoxPro
- IBM Db2
- PostgreSQL
Q.6: What is the flat file system?
Ans: Flat File System:
Early databases were relatively "flat," which means they were limited to simple rows and columns, like a spreadsheet. A flat file is the older version of a database. It stores data in a single table structure. Flat file databases are usually in plain text format, with only one record per line. The fields included in the record are separated using delimiters such as tabs or commas.
Q.7: Why Database Management System is preferred over Flat File System?
Ans: Advantages of Database Management System over the Flat File System
| DBMS | Flat File System |
|---|
| Multiple users can access data simultaneously | Only one user can access at a time |
| Capable of handling huge sets of data | Suitable only for smaller sets of data |
| Allows non-duplication and integrity | Increases duplicate and redundant data |
| Supports online access | Does not support remote connections |
| Good for small, medium, and large businesses | Limited only to smaller data management needs |
Q.8: Describe the characteristics of database management system.
Ans: Characteristics of Database Management System:
A DBMS is a modern version of database designing, organization, and manipulation. This mainly offers the solutions which a flat file system could not provide. The DBMS has many characteristics. Some of them are:
(i) Multiple users can access DBMS and can view, add, edit, and delete records.
(ii) A DBMS offers tools like Queues, Views, and Forms which help users to manipulate data easily and more efficiently.
(iii) A DBMS is more secure and reliable.
(iv) DBMS allows distribution of data in multiple tables by making use of features like keys and relationships between fields of those tables.
(v) This allows lesser duplication of data and results in lesser redundancy.
(vi) Preparing backups and providing limited permissions to the users are features of DBMS.
(vii) DBMS can handle large and complex data more conveniently. Therefore, it is preferred by medium and large organizations.
Q.9: Define and describe the basic components of DBMS.
Ans: Basic Components of DBMS:
The basic components of DBMS are discussed below:
(i) Table:
It is a collection of data elements organized in the shape of rows and columns. A contact list may be one of the simplest examples of a table. The marks record prepared by a class teacher is also an example of a table.
(ii) Field:
It is the smallest component in a database. It is where the actual data is stored during data entry. All data fields in the same table have unique names. Fields are also called attributes or columns. Multiple fields make up a data record, several data records make up a data record, several data records make up a data table, and several data tables make up a database.
(iii) Record:
A single entry in a table is called a record. Records are also referred to as tuples or rows. A record is made up of two or several data items which are also called tuples in a table representing a set of related data. For example, the given student table has four tuples/records/rows.
(iv) Data Types:
All fields in a table must have some data type. Data type is a data storage format that can contain a specific type or range of values. The data type of a field is a property that tells what kind of data that field can hold. Here are some basic data types.
| Data Type | Description | Examples |
|---|
| Integer | Holds only whole numbers | 145, -35, 74586 |
| Floating Point | Holds numbers with decimal points | 5.6, 3.14, 554.9 |
| Character | Stores only one character | A, B, C, D |
| String | Can store a combination of numbers, letters and special characters | Pakistan Computer, @admin |
| Boolean | Can hold only Boolean values i.e. true or false | 1, 0 |
| Date & Time | Stores date and time in specified format | 01-01-2020 11:30 |
Different DBMSs offer different range of data types to be stored. For example, MS Access allows a range of whole numbers from -32768 to 32767 for an "Integer". In modern DBMS, choosing proper data type is important to make sure that database runs faster.
(v) View:
In a database, the data is stored in tables. However, we can see that data through views. Views do not store data and just show the information virtually. They have the ability to fetch data from different tables. Views maintain the security of data and ensure that no changes occur in the original data.
Q.10: Why is it important to carefully decide the data type for each field?
Ans:
A data type determines the type of data that can be stored in a database table column. When we create a table, we must decide on the data type to be used for the column definitions. We can also use data types to define variables and store procedure input and output parameters. We must select a data type for each column or variable appropriate for the data stored in that column or variable. In addition, we must consider storage requirements and choose data types that allow for efficient storage.
Choosing the right data types for our tables, stored procedures, and variables not only improves performance by ensuring a correct execution plan, but it also improves data integrity by ensuring that the correct data is stored within a database. The data types of the fields determine what kinds of values we can store in them. If a field should only store a whole number, declaring it as an INTEGER prevents us (or a buggy piece of software) from writing some arbitrary nonsensical string into it, for example.
The data types of the fields also affect what operators and functions we can apply to them in queries. For example, we can take a substring of a string or convert a string to UPPER CASE, but we can't do either of those with a number. On the other hand, we can do arithmetic with numbers (add them, multiply them, etc), calculate the average, and so on.
The data type is also important when we ask the database to sort our result set. If we sort by a numeric column, it will sort numerically (smallest numbers first, or largest first if we specify that it should sort descending). If we sort by a string column, it will sort 'asciibetically' (character by character, with numbers coming before letters, like "apple", "banana", "carrot"; this can give us surprising and usually unhelpful results for strings containing numbers since "9" sorts after "1521", because '1' < '9').
Q.11: Describe the steps for creating a table using design view in MS Access.
Ans:
Steps for creating a table using Design View:
- To create tables in Access using "Design View," click on the Create tab and click on the Table icon. Then pull down the View menu and choose Design View.
- A new table then appears in the Table Design View. Note that the default name assigned to the table is Table 1.
- Type the name of a field into the "Field Name" column.
- Then use the drop-down menu in the "Data Type" column to assign the field a data type.
- If desired, type a description of the data stored in this field.
- Repeat steps 3 to 5 until we have created all of the necessary table fields. An example of a Table may be a customer Table that has the following entries:
| Field Name | Data Type | Description |
|---|
| Customer ID | Number | The unique identifier for a customer |
| First Name | Text | The first name of the customer |
| Last Name | Text | The last name of the customer |
| Address | Text | The address of the customer |
- Click the "Save" button in the Quick Access toolbar.
- Then type a name for the newly created table and click "OK."
Q.12: Write down the steps for creating a query of view using Design view in MS Access.
Ans:
Steps for creating a query of view using Design View:
- To make a query in design view, click on the "Create" tab in the Ribbon and pull down the "Queries" group and click on "Query Design" button.
- In the "Show Table" dialog box, add the table or tables that you want to add to query design view.
- Next, add the fields from these tables that we want to view in our query results or view. If we want to add all of the fields of a table into our result set, we can click and drag the first field in the table, shown as an asterisk.
- (iv) Once we have added all the necessary tables and fields to the query view, click the "Close" button in the "Show Table" dialog box to close it and display the query design view.
(v) To run a query and view the result set, we can click the "Run" button in the "Results" group of the "Design" tab in the "Query Tools" contextual tab on the office Ribbon.
(vii) Click the "Save" button in the Quick Access toolbar. Type a name for your view and click "OK" to save the query.
(vi) The result set looks like a table. This result set is a deflection of data from the selected fields of the table’s. It is also known as a view.
Q.13: What is the difference between Design View and Datasheet View?
Ans:
| Design View | Datasheet View |
|---|
| Design view allows us to create or change the table, form, or other database object, and configure the fields. | Datasheet view shows the data in the database. It also allows us to enter and edit the data. It does not let us change the format of the database, other than minor changes (such as displayed column widths). |
Q.14: What is data modeling? Name its important components.
Ans:
Data Modeling:
Data modeling is the process of developing a conceptual representation of data objects and their relations. Data models are used to express how the information will be stored in a database. This helps to identify the most important fields and remove the irrelevant data. Data models can be used by database developers to create a physical database. This saves a lot of time and effort for developers. There are three most important components of data models:
- Entity
- Relationship
- Referential Keys
Q.15: Define relationship and its types.
Ans:
Relationship:
When the database structures grew and became more complex, a lot of data started to become redundant, which means that data was being unnecessarily duplicated. This created a need to connect data entities instead of repeating the same data in multiple tables. This resulted in the creation of relationships and Relational Database Management Systems (RDBMS).
A relationship defines the connection between two tables. It creates a connection from an attribute of one entity with an attribute of another entity. Three types of relationships can be defined between entities.
One to One Relationship:
This type of relationship defines that a record in one entity can be connected to only one record in another entity. This is not a very common type of relationship because the data from related entities can directly be placed in a single entity. However, this type of relationship is used to divide larger entities into smaller ones.
One to Many Relationship:
This type of relationship defines that a record in one entity can be connected to many records in another entity. This is the most common type of relationship used in relational databases. This relationship can also be seen as a Many to One Relationship.
Many to Many Relationship:
In this type of relationship, one or more records of one entity are connected to one or more records of another entity. Usually, a third entity known as a "junction table" is used to create the many-to-many relationship between two related entities.
Q.16: Define entity with examples.
Ans:
Entity:
In a literal sense, an entity is any individual object that has its own qualities and properties. In database terms, an entity is an independent table, and its fields are known as attributes. As an example, a Payroll database will contain an entity named Employees. The Employees entity will contain various attributes like Employee ID, Name, Designation, salary, etc.
Q.17: What are referential keys? Describe its types.
Ans:
Referential Keys:
The relationships are configured by using referential keys on entities. The keys determine a certain set of rules that must be followed by the data stored in a field of an entity. In larger databases, keys are very important to uniquely identify a specific record.
Two types of keys are most commonly used in RDBMSs:
Primary Key:
A primary key is used to uniquely identify a record in an entity. When a primary key is applied to any attribute in an entity, it enforces the rules of Primary Key onto that attribute. These rules are:
- The attribute (field) must contain a unique value to identify a record. A unique value means that two records in the same entity cannot have the same value stored in this attribute where Primary Key is applied.
- The value of the attribute where Primary Key is applied cannot be null.
Foreign Key:
A foreign key is used to define the connection or relation between two entities. The foreign key of one entity is configured to be connected to the primary key of another entity. When a foreign key is applied on an attribute, it enforces that the value for that attribute should match any record in the related entity having a primary key.
Q.18: Write three benefits of using relationship in the database.
Ans:
A relationship is an important component of a relational database.
- It establishes a connection between a pair of tables that are logically related to each other. A pair of tables is logically related via the data each contains.
- It helps to further refine table structures and minimize redundant data. As we establish a relationship between a pair of tables, we will inevitably make minor modifications to the table structures. These refinements will make the structures more efficient and minimize any redundant data that the tables may contain.
- It is the mechanism that enables us to draw data from multiple tables simultaneously.
A properly defined relationship ensures relationship-level integrity, which guarantees that the relationship itself is reliable and sound.
Q.19: What is Entity Relationship or ER Model?
Ans:
Entity Relationship or ER Model:
Entity Relationship Model (ERM) or Entity Relationship Diagram (ERD) describes the entities, attributes, and relationships with their types in a simplified diagram. This diagram can itself be used as the reference for designing an actual database. It can even be used as a backup for the structure of a database. The ERD can be used in two ways:
- When the database has not been created yet. The ERD helps in creating a clear representation of the entire database based on user requirements.
- When an existing database needs to be documented. The Database development tool features automatic creation of ERD based on existing database which facilitates documentation.
Q.20: Write steps to design ER Model.
Ans:
Steps to design ER Model:
- Identify and design the entities based on the requirements of its users.
- Identify and design the attributes within the required entities.
- Identify the relationships required between entities.
- Design Primary Keys in interrelated entities.
- Design Foreign Key relationships based on requirements and bind them to previously created Primary Keys.
- Generate an automated Entity Relationship Diagram.
Q.21: Give examples to better understand relationship and referential keys.
Ans:
Understanding Relationship and Referential Keys:
The figure given shows four tables and their fields.
Students table is used to store personal information of individual students. It has an Id field set as a Primary Key. It also has a Class-Id field to set up a One-to-One foreign key relationship with the Class table.
Class table is used to store information about classrooms in a school. It has an Id field set as a Primary Key. A student can be enrolled in only one class; hence, the Students table has a One-to-One relationship with the Class table. However, many teachers can be associated with many classes. This requires a Many-to-Many relationship between Class and Teachers tables.
Teachers table is used to store personal information about a teacher. It has an Id field set as a Primary Key. Many classes can be taught by many teachers. This requires a Many-to-Many relationship between Teachers and Class tables.
TeachersClass table is used as a junction table to facilitate the Many-to-Many relationship between Teacher and Class tables. It also contains an Id field set as Primary Key. The other two fields are used to define which teachers are associated with which classes. It creates a One-to-Many relationship with each of the two connected tables. Teachers and Class tables use their Teachers Class Id field's foreign key relationship to fetch the related information from this table.
Q.22: Describe the components of ER diagram.
Ans:
Components of ER Diagram:
ER Design is made up of different components like attributes, relationships, etc. There are defined symbols and shapes to represent each one of them. Some of the shapes used to define these components are:
| Symbol | Description |
|---|
| Rectangle | A rectangle is used to define an entity. This can be any real-world object like Student, Teacher, Class, etc. |
| Ellipse | An ellipse defines an attribute of an entity. One entity may contain multiple attributes and are defined by multiple ellipses. |
| Diamond | Relationships are symbolically represented by a diamond shape. It simply states the type of relationship between two entities. |
| Lines with 1 and M | Connecting lines show the type of relationship between two entities. These lines are annotated by 1 or M (stands for Many) at their ends to describe the type of relationship. |
Q.23: Give an example of ER Model.
Ans:
For example, a sample ERD for the statement "A writer creates a novel and consumer buys novel" is given below.
Here in this example, the diagram shows that:
- Entities are in rectangular boxes:
- (1) Writer
- (2) Consumer
- (3) Novel
- Relationships are in diamond shapes:
The diagram representation is shown as:
- Writer → Creates → Novel
- Consumer → Buys → Novel
Q.24: Discuss the importance of ERD in business.
Ans:
(i) Entity relationship diagramming is functional as a method of better visualizing data. Every manager of a multinational enterprise knows that spreadsheets are pretty much useless when it comes to understanding the overall picture of their entity's operations and how they are structured in relation to each other. This is because human beings do not generally learn and process by mere letters and numbers - nearly two-thirds of all human beings learn and memorize visually, through seeing the information presented in an accessible form.
ERD takes advantage of the basic learning strategy that's programmed into our minds by extending the programming into compatible and digestible visualizations of the relationships making up our entity. Software applications that diagram entity relationships are meant to make those relationships come to life on the screen (and in our mind), thus making them much easier to understand.
(ii) It’s only with ERD software that senior management, board, and stockholders will be able to assess the whole business and to plot a viable strategy. Basically, an ERD's function is to bring all of that data stored in our servers to life so we can make sense of it.
An ERD tells a story about our entity's current state. Stories are how we learn the best, so it's also an essential part of how we analyze events and plan for the future. So diagramming should not only show what the current state of affairs is, but it should also show the potential ways structures and organizations within an entity can be altered and the potential effects of these changes. For groups that operate with hundreds or thousands of interconnected entities on a multinational scale, we can see how, without visualization, nothing would make sense.
