Database Management Skills

 

INFORMATION TECHNOLOGY 

SKILL COURSE 
SUBJECT CODE- 402 

PART-B Subject Specific Skills 

Unit-3 Database Management Skills 

• Database organizes data in the form of tables so that it's easier to make manipulations in the data. A table is made up of rows and columns. 

DATABASE OBJECTS

• TABLE:  A table stores data in a structured format in the form of rows and columns. 

1) Records: A row in a database is referred as a record. A record is a set of related information that belongs to a particular object. A record is also called tuple. 
2) Fields: A column in database is referred as a field. A field consists of the specific information related to the particular category.

 

• QUERIES: It is used to ask question and retrieve data from the database according to specific criteria. 
• FORMS: It has an attractive interface that accepts data from the user. It is used to update any record in table or query at a time.
• REPORTS: It is used to display the data in printable format. 

• A database is used by developers, administrators and end users. 

• DBMS is a computerized software that helps in maintaining records of the data and enables to create, edit, modify and present data in a structured format. It can be further divided into two types:

a) Flat File Database: It is used to maintain less amount of data as it stores the information in a single table.
b) Relational Database: It is used to maintain huge amount of data as it stores the information in multiple tables by establishing links between them. 

ADVANTAGES OF DBMS

1. Data Standards: A database always holds data in a structured format i.e. in the format of date, time, etc. 
2. Data Availability: User have an access to the database anytime and anywhere. 
3. Reduced Data Redundancy: Data redundancy occurs when same piece of information is duplicated unnecessarily multiple number of times. DBMS helps in preventing this. 
4. Prevents Data Inconsistency: Data inconsistency occurs when same data is present in different formats in multiple tables. DBMS takes care of this too. 
5. Helps in maintaining Data Integrity: Data integrity refers to maintaining and assuring the accuracy and consistency of the data. 

      6.Ensures Data Concurrency: Data concurrency means many users can access and modify the data at the same time without violating data consistency.

      7. Takes care of Data security:  DBMS provides with the facility of granting specific permission to the specific employees preventing loss of data to unauthorized user access. 

      8. Backup and Recovery Management: There are various strategies and procedures that prevents any loss of data.

KEYS

• PRIMARY KEY: A primary key is a field that uniquely defines a record in the table. 
• COMPOSITE KEY: A composite key or composite primary key refers to the combination of two or more fields that together define the record uniquely in the table.
• FOREIGN KEY: A foreign key is a field that is used to set relation between two or more tables. 
• CANDIDATE KEY: A candidate key is used to refer to the fields that are eligible of becoming a primary key. 

INTERFACE OF MS ACCESS 

• Backstage view
• Quick Access Toolbar
• Title Bar
• Navigation Pane
• Documents Tab 
• Ribbon

CREATING TABLE 

1. Using Design View
2. Using Datasheet View 

PERFORMING OPERATIONS ON TABLES 

• Inserting Data in table 
• Editing Records in table
• Deleting Records in table 
• Sorting Data in table 

Inserting Data in Table 

Sorting the Data in Table 

REFERENTIAL INTEGRITY

Referential Integrity is a rule in DBMS that prevents data inconsistency. It helps in establishing link between two or more tables and fetching data. It stated that a foreign key must have a matching primary key and prevents the user from accidently making any changes that may lead to data inconsistency. 

RELATIONSHIPS

1. One-to-One: In this type of relationship, a record in a table can have only one matching record in the other table or vice-versa.
2. One-to-Many: In this type of relationship, a record in a table is related to many records in the other table. 
3. Many-to-Many: In this type of relationship, multiple records in a table are associated with several records in the other table. 

CREATING QUERY

1.  Using Query Wizard 
2.  Using  Query Design 

PERFORMING OPERATIONS ON QUERY 

1. Saving the query 
2. Editing the query 
3. Specifying the query 
4. Performing Calculations 
5. Using functions in queries 
6. Grouping Data 

SPECIFYING THE QUERY 

1. Criteria for single field 
2. Criteria for multiple fields 
3. Criteria using wildcards: Wildcards are special character that are used substitutes for one or more characters in a string. These are helpful for locating multiple items based on a specified pattern, making the search operation easy.

Query in single field using design view 

Query output of single field using design view

 

STRUCTURED QUERY LANGUAGE (SQL)

Structured Query Language (SQL) is the language in RDBMS for writing queries. Using SQL, a user can create queries to fetch and manipulate the data of the database. 

The SQL commands are of two types: 

• DATA DEFINITION LANGUAGE (DDL): These commands are used to define and modify the structure of a database.  The commands that fall under this are: 

          1) Create      2) Alter      3) Drop       4) Truncate       5) Rename 

• DATA MANIPULATION LANGUAGE (DML): These commands are used for editing or modifying the data in the database.

        1) Select       2) Insert     3) Update     4) Delete 

COMMONLY USED SQL COMMANDS 

1) CREATE COMMAND: 

Create Table Table_name (

Field_name1 Data type,

Field_name2 Data type,

Field_name3 Data type,...);

2) INSERT COMMAND:

Insert into Table_Name (

Field1, Field2, Field3,...) 

Values (Value1, Value2, Value3,...);

3) SELECT COMMAND:

 

To select all the records: Select*from Table_name;

To select specific fields: Select column1, column2,.. from Table_name;

Using 'where' clause: Select column1, column2,.. from Table_name;

Using 'order by' clause: Select column1, column2,.. from Table_name order by condition;

    

4) UPDATE COMMAND:

Update Table_Name 

Set column1=value1, column2=value2,...

where condition;

Update command

5) DELETE COMMAND:

Delete from Table_Name

where condition;

6) DROP COMMAND: 

Drop Table Table_Name;

FORMS

A form in an attractive interface that accepts data from the user. It allows you to add or update one record at a time. A form can have a different or same name as the table.  Access provides with three main views to display the form.

1. Form View:  This view is used to enter or edit data. 
2. Design View: This view is used to adjust the design of your form.
3. Layout View: This view is used to change the look and feel of the form. 

A form can be created using Form wizard. 

Create tab > Forms Group > Form Wizard 

PERFORMING OPERATIONS ON FORM

• Entering or Removing data in forms
• Searching records in form
• Modifying forms: 

1) Changing the properties of label

2) Changing background color of form 

REPORTS

A reports is a summary of the database. It is an effective way to organize and summarize the data for viewing and printing. Reports also provides the facility to group and sort the items, thus making it much more readable.  

A report can be created using Report wizard.

Create tab > Reports Group > Report Wizard

REPORTS SECTION

• Report Header
• Page Header 
• Detail
• Page Footer 
• Report Footer