Grade 10 Database (Lesson 09) ehglish
Database
Defining a Database - Collection of organized data can be defined as a database
Manual database
Higher the
amount of data it becomes difficult to keep in our memory. On such occasions
writing these data is one method. That is called manual database.
Electronic database
The other
method is to maintain the data on an electronic medium.
Advantages of
Electronic Databases
·
More efficient in retrieving information.
·
Easy to obtain copies.
·
Smaller physical space to store data.
·
Data analysis can be carried out efficiently.
·
Data can be shared.
·
Independence of
data. Though database and software are connected, database tables can be updated
without changes to the application software
Features of a
Database
·
Minimal data redundancy- If the same data is stored in several tables, this is
called data redundancy
-
By storing related database in tables, data redundancy
can be minimized.
-
·
Data consistency : By minimizing data redundancy, the same data being saved in various tables
can be reduced or prevented. This enables to maintenance of data consistency
·
Increases
efficiency : Since database
tables are properly organized, saving and reading data can be carried out efficiently.
·
Increases accuracy
: Since data redundancy can be minimized in a database,
data
consistency is maintained. This increases accuracy
·
Increases validity : Properties of fields can be assigned when a database
is planned. Validity can be checked at the data entry
stage.
·
Security : Unauthorized
access can be controlled by using a password and encrypting the database.
comparison
of electronic and manual databases.
Introduction to Relational Database
-
A common data for a
object or a person called field
-
A collection of fields related to one person
or object is called a record.
-
A collection of
records is called a data table.
-
A collection of
related tables is called a relational database.
Primary Key
A column (or a combination of columns) which enables to
identify a record uniquely is called primary key Special attributes of a primary key
• Should not be empty (data being there is mandatory)
• No duplicate values (same values do not exist)
Composite Primary Key
The primary key which consists of two or more columns of
a table uniquely identifies a record is called Composite Primary Key.
Foreign Key
If a field (or a combination of fields) of one table can
be used to uniquely identify rows of another table, this/these field/s is/are
called the foreign key.
This key builds connection of two tables.
Data
Duplication
Recording
column data repeatedly is called data duplication
Disadvantages
of data duplication
(i) Inability to select a primary key field to
maintain identity of records.
( (ii) Inability to
analyse data properly
Though the number of students who have scored above 60
marks is 5 as per the above table, the actual number of students in the class
is 3.
(iii)
Reduced efficiency
(iv)
Possibility to get inaccurate data is high.
(v)
Difficult to input data.
To enter the marks of a student, his/her name and date of birth should be entered
repeatedly
(vi)
Possibility to make errors in deleting data.
(vii)
Difficult to update data..
These difficulties
are created as all data are stored in one table. Hence, to minimize such
difficulties, a table can be divided into several tables
Database
Management System-DBMS
A collection
of objects which facilitates planning and
maintenance of the database to the user is called a DBMS. This consists
of several objects tables,
queries, forms and reports
Database
Management System Software
Using of Database Management System Software
Step 1 Starting DBMS
software
Start→ Programs → Microsoft Office → Microsoft Office
Access → Blank Database → Select Folder and Type Name → Create
Step 2 Planning a
Data Table
Create → View→ Table Design → Give the
field name and data type →
Design properties → Save the table
Data
type : a type of data
related to one field is called a data type
text - composed
of letters, numbers (not used for calculations )or special characters (Eg- %,
*, -).
Memo-
Long Text
Number-
used for calculations . plus or minus whole numeric
figures, plus or minus numbers with
decimal
places
Date/time - indicates dates and time
Currency - used to indicate monetary values.
Auto Number - A unique sequential
(incremented by 1) number
Yes
/No - fields that contain only one of two values (Yes/No)
OLE
Object - Microsoft Excel spreadsheet, a Microsoft Word document, graphics,
sounds, or other
binary data
Step 3 Creating the
Primary Key for a Data Table
Data should
not be duplicated to uniquely identify rows of a table. Identity of records can
be maintained by using a primary key field.
Select the row/
rows that should be made as Primary key → Click on Primary key → save the table.
Step 4 Entering Data to the Table
Designing Query to Get Information
Let us use a
query to obtain necessary information from the data of related tables.
Create Tab →
Query Design → Select Table or Tables' →Select Fields form
Table or Tables → Type Name and save →Run Query
Attachment 01
Designing Reports to Present Information
Reports are
used to obtain printed copies of important information received from the data
of related tables. Both tables and queries can be used to design reports
Create Tab →
Report Wizard → Select the necessary table or query →Select the fields needed
for the report from the table or query → Design the report as you need clicking
Next button → Finish.
Let us learn
how numerical values included in a report are calculated.
- First change from Report
View to Design View.
- Then you will get a report
layout
- Select the tool given as
‘ab’ on the tool brand establish the text box under Report Footer.
- The text box is indicated
as unbound. The reason for this is there is no field given to it.
- To get the total of admission fees,
get the Expression Builder Dialogue Box to build expressions by clicking the
button with three dots in front of the Control Source on Property Sheet
- To get the total of
admission fees, type the expression
= Sum ([Student Table]![Admission
Fees]) and type OK. Then change the view from Design View to Report View.
Creating Forms to Entering and Displaying Data
Forms are
used to enter data to a table in Database Management Systems. Further, forms
are used to display entered data as well.
1. Building a Data Form
Create →
Form Design → Add Existing Fields → Show tables → Select the required table →
Drag table fields to the form
Each record
of the table can be filled one by one through the form. For this, the Record
Navigation Bar of a form is used.
2. Showing Table Records in Data Form
Home Tab →
View → Form View
3. Entering Control Wizards to a Form
Design Tab →
Select 'Control Wizards' → Select the button indicated as XXXX → Establish the button
in data form → Select Record Operation in Command Button Wizard → Select the
Action which should be entered (E.g.: Add New Record) → Give a proper name to
the button → Finish
Creating a Relational Database
Rather than
keeping all the data in a single table, it is easier, more efficient and productive
to input, update and delete data if
those are recorded separately indifferent tables. Hence, let us study this
section well to break a data table into several simple tables and to build
relationships between these.
several type
of relations can exist among tables. They are
1.
One-to-one
2.
one-to-many
3.
many-to-many.
One-to-one relationship
If only a
row of a table is related to a row of another table, it is called a one-to-one
relationship.
Given below
is Table which includes the marks students scored of the Grade 5 scholarship
exam.
Build
Relational Database. Draw diagram to represent one-to-one relationship
Creating a
Relational Database Using Database Management System Software
Step 1 Design tables separately
Select
Database Tools → Select Relationship command → Select the table which should
be related →
Click 'Add' command button.
Drag the
Primary Key to the foreign key of the
table which should be related
| ||||||
Special Features in Related Tables
-
As soon as the values of a primary key field of the
Student Table are updated using 'Referential Integrity,' the foreign key field
values of the related table are automatically changed.
-
Further, when a record of the Student Table is deleted,
the related records of the related table are automatically deleted.
One-to-many Relationship
If each
record of a table is related to several records of the other table, this is
one-to-many relationship.
Example
Refer Table
given below which includes personal data of students and payments of service
and facility fees. Build Relational Database Draw diagram to represent one-to-many
relationship
-
In this table, the fields 'Payment Date' and 'Fees'
depend on the Field Receipt Number. Further, data is not unique in the
Admission No Field (data duplication).
-
There can be incomplete records in the table as receipts
should be issued in the order of payment. (Indicated as R in the table) Hence,
let us break this table into two tables
If a record
of a table is related to several records of another table and if a record of
that related table is related to several records of the other table, there is a
many-to-many relationship between those tables.
Example
Students
take part in sports (team) as an extracurricular activity. There are sports such
as cricket, volleyball and football. A student can do at least one of these
sports. Further, there should be a team for each sport. Refer Table given below
which includes these data. Build Relational Database Draw diagram to represent one-to-many
relationship
Comments
Post a Comment