1st Year Computer Science Chapter Wise Notes – Chapter 8 Database Development

Q.2 i) Name different types of database management systems.

Answer:
Database management system:
DBMS stands for Database Management System. It is defined as:
“A collection of programs that enables user to store, modify and extract information from a database is called DBMS.”
There are three types of database management system which are used commonly. These are:
a.       Microsoft Access
b.      SQL Server
c.       Open Office Base

Q.2 ii) What is Open Office Base?

Answer:
Open Office Base is an open-source office suite. It is fully featured database management system.  It is an open source application program. For creating database design easily i.e. to create tables, forms, reports etc. it has wizards for help of users. Interactive databases are created in it  that can manage data related to payroll, inventory, assets, budgets, customers, sales orders and invoices etc.

Q.2 iii) How forms are useful in database?

Answer:
Forms
Definition:
“The interfaces used to work with data.”
Forms often contain command buttons that perform various commands. Database can be created without using forms. In that case, data will be edited in the table datasheets. But it is preferred to use forms in database for ease for viewing, entering and editing data in the tables.
Forms are useful as they provide an easy to use format for working with the data and some functional elements can also be added. Such as, command buttons. These buttons can be programmed to determine which data appears on the form, open other forma or reports, or perform a variety of other tasks. For example, in “Customer Form” is for the working of customer data. The customer form might have a button which opens and order form where new order for a customer will be added.
A form also allows us to control how other users interact with the data in the database.
Example:
For example, form can be created that shows only certain fields and allows only certain operations to be performed. This helps protect data and to ensure that the data is entered properly.

Read more: Free Computer Science Notes Khyber Pakhtunkhwa Textbook board Chapter 7 Database fundamentals

Q.2 iv) Name common Access database objects.

Answer:
Access database consists of various objects that are used to design database. Some of them that are used commonly are as follows:

•  Tables
•  Queries
•  Forms
• Reports

Q.2 v) How primary key is assigned in a table?

Answer:
Primary key of relational table uniquely identifies each record in the table. It is a field which is also called a unique identifier of table. It can either be a normal attribute that is guaranteed to be unique such as student Roll No etc.

Primary key is assigned in a table, in following way:

• Right click on the table that we want to set the primary key and then click Design View on the shortcut menu.
• Select the field that we want to use as the primary key e.g. CustomerID
• On the design tab, in the tools group, click primary key as shown in figure below:

A key appears to the left of the field that we specified as the primary key as shown below:

Q.2 vi) What is a report?

Answer:
Reports
Definition:
“The summarization and presentation of the data stored in the table.”
A report usually answers a specific question. Such as, “How much money did we receive from each customer this year?” or “What cities are our customers located in?” Each report can be formatted to present the information in the most readable way possible.
A report can be run at any time and will always reflect the current data in the database. Reports are generally formatted to be printed out, but they can also be viewed on the screen, exported to another program, or sent an email message.

Q.2 viii) Name different navigation buttons in a form.

Answer:
Navigation buttons are used to navigate records in a form. The names of navigation buttons used in form are as follows:

Extensive Questions

Q.3 i) Explain the following database objects.
a) Tables                
b) Forms
c) Queries              
d) Reports

Answer:
Tables:
Table is a basic object of a database. It is used for storing data and data is retrieved from table than. It can be defined as:
“A collection of related data organized in rows and columns.”
A database table is similar in appearance to a spreadsheet as in that data is stored in rows and columns. As a result, it is usually quite easy to import a spreadsheet into a database table. The main difference between storing your data in a spreadsheet and storing it in a database is in how the data is organized.
Each row in a table is referred to as a record. Records are where the individual pieces of information are stored. Each record consists of one or more fields. Fields correspond to the columns in the table. For example, if there is a table named as employee, then each row will contain a record of different employee and each field of row contain different type of information of an employee. Such as, first name, last name, address and so on.
Fields must be designated as a certain data type i.e. numeric, text, date or time or some other type.
Example:
Table is displayed in figure below:

In the above figure of tables:

1. A table is created in database.
2. In first column, its field names are specified.
3. In second column, its data type is selected.
4. In dialog box at the bottom of window, in general tab, the properties of each field are specified i.e. its size, its caption, its default value etc.
5. Then table will be saved by giving a proper name to it.
6. The table is created in database.

Query:
Queries are the real work in the database and can perform many different functions. It can be defined as:
“A statement that extracts information from the database.”
The most common function of queries are to retrieve specific data from the tables. The data user want to see is usually spread across several tables and queries allow user to view it in a single datasheet. It also work when user don’t want to view all records at once. Certain queries are updatable, means they can edit the data in the underlying table via the query entered.
Queries come in two basic varieties:

•  Select query
• Action query

Select query:
A select query simply retrieves the data and makes it available for use. The results can be viewed by this on screen, print it out or copy it to the clipboard.
Action query:
As its name implies it is used to perform different actions with data. Action can be used to create new tables, add data to existing tables, update data or delete data.
Example:
Query is shown in figure below:

Read more: 11th Class Computer Science Notes Chapter 6 (Wireless Communications)

In this example:

1. The query is shown here.
2. In the bottom window, all the information about query is given i.e. which fields are included in query and from which table.
3. After this the query is saved and run to get the required result.

Forms:
Forms are defined as:
“The interfaces used to work with data.”
Forms often contain command buttons that perform various commands. Database can be created without using forms. In that case, data will be edited in the table datasheets. But it is preferred to use forms in database for ease for viewing, entering and editing data in the tables.
Forms provide an easy to use format for working with the data and some functional elements can also be added. Such as, command buttons. These buttons can be programmed to determine which data appears on the form, open other forma or reports, or perform a variety of other tasks. For example, in “Customer Form” is for the working of customer data. The customer form might have a button which opens and order form where new order for a customer will be added.
A form also allows you to control how other users interact with the data in the database. For example, form can be created that shows only certain fields and allows only certain operations to be performed. This helps protect data and to ensure that the data is entered properly.
Example:
Form is shown in figure below:

In this example:

1. The information of the Table 1 is shown in the above figure of form.  The table that was created first and then queries are implemented on that table. The result of that query is shown in the form.
2. Forms can be of different colors. Different types of buttons and other objects are included in it, to make the form attractive. Like in the above figure, background color of form is changed, and controls like combo box is added to the form.

Reports:
Reports can be defined as:
“The summarization and presentation of the data stored in the table.”
A report usually answers a specific question. Such as, “How much money did we receive from each customer this year?” or “What cities are our customers located in?” Each report can be formatted to present the information in the most readable way possible.
A report can be run at any time and will always reflect the current data in the database. Reports are generally formatted to be printed out, but they can also be viewed on the screen, exported to another program, or sent an email message.
Example:
Report is displayed in figure below:

In above example:

1. The information of selected table or fields is displayed.

2. This information is printable and one can print it to get the hardcopy of all information.

Q.3 ii) Explain different data types used in Access.

Answer:
MS access has following data types:
Text
It the default data type used in MS access. It used for entering text or combinations of text and numbers. It’s limit of entering text is up to 255 characters.
Memo
It is also used for entering text but of larger amounts. It can store up to 65,536 characters. So, it can be used for detailed descriptive writings.
Number
This data type is used for storing mathematical numbers. Several field sizes are available for numbers.
Auto number
This field automatically gives a number to the new record which has been entered. It usually starts numbering from 1. Its storage space is up to 4 bytes.    
Yes/No
This data type option is used for entering Yes/No, True/False, On/Off or other values that must be only of two. Its storage space is 1 bit.
Currency
This data type is used for amount of currency. Currency is written is special way with specific comma and decimal positions. So, this field automatically adjusts comma and decimal to their correct positions. It can hold up to 15 digits with dollar ($) sign and 4 decimal places. Its storage space is up to 8 bytes.
Date/Time
This field is used to enter date and time, separately or in combination. Its storage space is upto 8 bytes.
OLE object
An OLE (Object Linking and Embedding) can store, pictures, audio, video or other objects such as word document or Excel spreadsheet that is created in another program.  Its storage space is up to 1 GB.
Hyperlink
This data type contains links to other files, including web pages. A hyperlink address has up to four parts: the text that is displayed in the field, the path to a file or URL, a sub-address which is a location in the file or page in the website, and the text that is displayed as the tooltip.

Read more: FA Part-1 Computer Science notes Chapter 4 (Inside System Unit)

Q.3 iii) Describe two ways of creating a table in Access.

Answer:
In a relational database, tables store our data. If we don’t have tables, we don’t have a database.
Access gives us several ways to create tables, these are:

• Datasheet view
• Table templates
•  Design view.

In Datasheet view, we build a table by entering field names and setting data types manually. All we have to do is to click and type.
Table templates are pre-made tables that meet several common business needs. For example, the Assets table template contains many of the fields, such as Item and Purchase Date etc.
Design view lets  uscontrol every field and property in the table.
Here we will create tables by using Datasheet and Design view.
Creating a database:
This procedure of creating database is explained below:

•  In the left pane, the templates categories- select from Microsoft Office online.
•  It will display database templates in right pane of window, select Blank database from here. As show in figure below:

The option of renaming the database will appear as shown in figure below. Here give meaningful name to database.

• Click create to finish naming the database.

Create tables in datasheet view:

• When create button is pressed the table is opened in Datasheet view by default as shown in figure below:

• Now add fields to table and then for each field, assign the required datatype.
• Select the first field of table as shown below:

• By default the table name is Table1. Change the table name according to database requirement.
•  Click Ok. The table is saved and created in datasheet view.

Create tables in design view:

• To create a table, select the Create tab in the toolbar at the top of the screen.
• Click on the Table Design button in the Tables group, as shown below:

Add the fields to the table. For example, in the figure below fields of Customer_ID, First_Name, Last_Name etc.

Q.3 iv) Explain the process of creating forms in Access.

Answer:
Forms provide a window in our data, which makes the database look polished and professional and also helps us to enter data more accurately.
Creating forms:
Forms are created in Access by using several automatic tools for creating forms. These tools are located in the Forms group on the top Create tab in the Ribbon. It is shown as under:

These tools include:

•  The Form option is used to make a basic form. It shows a single record at a time.
• The Split form option creates a form showing one record on top and includes the Datasheet view of the entire source table on the bottom.
• The Multiple Items option creates a form that shows all records at once. These records look similar to the source table in Datasheet view.
•  The Form Wizard is hidden under the More forms option. It creates form by the process of creating more customized forms.

Create form using Form option:
It is the basic option for creating Form. It will show just one record at a time. It also includes all fields in source table that can be modified by the us to add controls or hide fields.
Form can be created in following steps:

Read more: KPK 1st Year 11 Class Computer Science Notes Chapter 3(Central Processing Unit)

•  Highlight the table that we want to use as the source table.
• With the source table highlighted, select the Form option from the Forms option group in the Create tab on the Ribbon.
• The new form is created and opens in the object pane. The form created is shown in figure below:

Q.3 v) Write the steps for creating relationships between tables.

Answer:
Relationships:
Relationships is the link or a situation in a database which relates data of two tables with each other. It associates a primary key of one table with foreign key of another.  It allows a relational databases to split and store data in different tables while linking their data items.
Creating relationships:
Relationship between two tables are created in following steps:

• Click the Relationships command in the Show/Hide group on the Database Tools tab in the Ribbon. It is shown in figure below:

• When the Show table dialog box appears:

        o   Select each table name, then click Add for tables that we want to relate.
        o   After tables are added close the Show table dialog box. It is shown below:

Now Relationship map is displayed that contains all the tables as shown below:

• For creating relationships move the mouse pointer to the primary key of first table and drag it to the foreign key of another table. Like in figure below, Customer ID is the primary key in Customers table and forgein key in Orders table. Book ID is primary key in Books table and it is froeign key in Orders table. 
•  Here we drag the first field name onto the field name we want to relate by releasing the left mouse button. In the example shown, the Book ID field from the Books table and dragged and dropped it on the Book ID field in the Orders table.
•  Then the Edit Relationships dialog box appears, as shown below:

• Select the Enforce Referential Integrity option.
•  Click Create and the relationship is created.

Q.3 vi) Explain different types of queries used in Access.

Answer: 
Queries help to select information from tables and queries for a specific purpose. It selects columns from records, records from table.  We can select, summarize, update, delete, make new table, append records to table using query.
Types of queries:
There are three types of queries in Access:

•  Select
•  Update
•  Delete

Select Query:
The select query is the simplest and most common type of query available in MS-Acess. This query is used to select and display data from either one or more tables depending upon the requirement. Select query creates a vitual table where the data can be changed once record at a time.
Query can be created by using Query Wizard and also by Design view. But the basic steps are same. These are as follows:·    

• Pick the tables or questions that we need to use as sources of information.
• Determine the fields that we needs to include from the information sources i.e. tables or queries.
• To determine the criteria of query is optional. It is to limit the records that the question returns.
• After the query is made it can be run to see the results.

Updata Query:
The update query allows for one or more field in tables to be updated. In other words, it can be said that the update query allows to make global changes to data in one or more tables.
Update query uses WHERE clause to modify the table. The values that can be updated by using this query are:

• All records have same values.
• A field value from linked table.
•  An expression that is based on values in the table or linked table.
•  A value of function that indicates field values as its parameters.
•  Those functions which are defined by user that may or may not include field values as parameters.

Delete Query:
The delete query used to remove one or more rows of data from a relational database table.  It uses Where clause to determine the criteria of query. In delete query:

• The * with table name will delete all its records.
• The * character with where clause will give its some criteria to delete.
• If that table is linked with some other table than the records of that table will also be deleted.

Q.3 vii) How can one-to-many relationship can be created between two tables? Give example.

Answer:
One-to-may relationship is a degree of relationship in which one attribute of an entity or table is associated with one or many attributes or instances of another entity. For example, in the example explained here Customers entity is making one – to – many relationship with Orders entity. It is to express that one customer can place many orders or same order can be placed by many customers.

To create this relationship, we have follow these steps:

Read more: KPK Class 11 Computer Science notes Chapter 2 (Computer Memory)

•  Open relationships window by clicking relationship icon from Database tools tab, shown in figure below:

   It will open the Relationship window, shown below in figure: