Database Systems | Quiz 1

Introduction to Database | Week 1

Data

• Raw facts

• Facts that have yet been processed to reveal their meaning to the end user

• It is the foundation of information, which is the bedrock of

  knowledge—that is, the body of information and facts about a

  specific subject.

• Constitutes the building blocks of information.

Information

• The result of processing raw data to reveal its meaning.

• It is produced by processing data.

• It is used to reveal the meaning of data

Database

A shared, integrated computer structure that houses a collection of related data.

End-user Data

Raw facts of interest to the end user

Metadata

• Data about data

• Through which the end-user data is integrated and managed.

Database Management System (DBMS)

It is a collection of programs that manages the database structure and controls access to the data stored in the database.

1. Data

2. Hardware

3. Software

4. End Users

5. Procedure

Components of the DBMS Environment:

1. Users can define the database through a Data Definition Language (DDL).

2. Users can manipulate data through a Data Manipulation Language (DML).

3. DBMS can provide security system.

4. DBMS can provide an integrity system.

5. DBMS can provide a concurrency control system.

6. DBMS can provide a recovery control system.

What are the different facilities that DBMS provide?

File Systems

Collection of application programs that perform services for the end users (e.g. Reports)

Database System

• Refers to an organization of components that define and regulate the collection, storage, management, and use of data within a database environment

• It is an automated system that enables users to define, create, maintain and control access to the database.

Entity Relationship Model

• Introduces by Peter Chen in 1976

• Yields a graphical representation of entities and their relationships in database structure

• Usually represented by an Entity Relationship Diagram

• Introduced Three Types of Relationships:

  1. One-to-Many (1:*)

  2. Many-to-Many (*:*)

  3. One-to-One (1:1)

• Based on the following components:

  • Entity

  • Attributes

  • Relationship

Relational Database

It is divided into logical units called table which is composed of rows and columns of data.

Relation

It is composed of rows and columns of data.

Attribute

In relational database, the table columns correspond to attributes.

Domain

It refers to a set of valid atomic values for a given attribute.

Primary Key

It refers to an attribute or field that serves as a unique identifier for a particular record within a relation.

Schema

It is the over-all design of a database.

Structured Query Language (SQL)

• The standard language used to define, query, update and maintain relational databases.

• Must be portable; that is, it must conform to some basic standard, so a person does not have to relearn the basics when moving from one RDBMS to another.

• Data Definition Language (DDL)

• Data Manipulation Language (DML)

• Data Query Language (DQL)

• Data Control Language (DCL)

• Transactional Control Commands (TCC)

Main categories of SQL commands are as follows:

Data Definition Language (DDL)

• Used to define the database structure or schema.

• It is also used to specify additional properties of the data.

• Allows the user to create and restructure database objects.

• SQL includes commands to create database objects such as tables, indexes, and views, as well as commands to define access rights to those database objects.

• The storage structure and access methods used by the database system by a set of statements in a special type of DDL called a Data Storage and Definition Language.

Domain Constraints

A domain of possible values must be associated with every attribute

Referential Integrity

There are cases where we wish to ensure that a value appears in one relation for a given set of attributes also appear in a certain set of attributes in another relation i.e. Referential Integrity.

Assertions

It is any condition that the database must always satisfy.

Authorization

We may want to differentiate among the users as far as the type of access they are permitted on various data values in database.

• Read Authorization

• Insert Authorization

• Update Authorization

The most common being of Authorization:

Read Authorization

Allows reading but not modification of data

Insert Authorization

Allows insertion of new data but not modification of existing data

Update Authorization

Allows modification, but NOT deletion

1. CREATE

2. ALTER

3. DROP

4. TRUNCATE

5. RENAME

6. COMMENT

Data Defniton Language (DDL) statements are used to define and manage database structures. Here are some common examples of DDL statements:

CREATE

Used to create a new table, database, view, or other database objects.

ALTER

Used to modify an existing database object, such as adding or dropping columns in a table.

DROP

Used to delete an existing table, database, or other objects from the database

TRUNCATE

Used to remove ALL RECORDS FROM A TABLE, but NOT THE TABLE ITSELF

RENAME

Used to rename a database object like a table or a column

Data Manipulation Language (DML)

• Used to query and modify database data

• Allows the user to manipulate data within objects of a relational database.

• SQL includes commands to insert, update, delete, and retrieve data within the database tables.

• Are executed within the context of a transaction, which is a logical unit of work composed of one or more SQL statements, as defined by business rules.

SELECT

• To query data in the database

• Retrieve data from the database

INSERT

To insert data into a table

UPDATE

• To update data in a table

• Update existing data within a table

DELETE

• To delete data in the database

• Deletes ALL RECORDS from a table, space for the records remain

1. Procedural DMLs

2. Declarative DMLs (also called as Non-Procedural DMLs)

DML are of TWO TYPES:

1. SELECT

2. INSERT

3. UPDATE

4. DELETE

5. MERGE

6. CALL

7. EXPLAIN PLAN

Common DML Commands:

Data Control Language (DCL)

• Allows the user to create objects related to user access and also control the distribution of privileges among users.

• Are used to control access to data objects, such as giving a one user permission to only view the certain table, and giving another use permission to change the data in the given table.

• Encompasses a collection of SQL commands designed to allow the granting or revoking of privileges to database users.

1. GRANT

2. REVOKE

What are the DCL Commands?

GRANT

• Granting rights

• Administrators have the discretion to grand privileges covering various crucial aspects of data handling

REVOKE

• Removing Rights

• It is instrumental in withdrawing privileges that have been previously granted.

• It plays a crucial role in ensuring that users possess only the rights required at any given moment.

Data Query Language (DQL)

Allows the user to select a data or a group of data.

Transactional Control Language (TCL)

• Are used to manage transactions in the database.

• These are used to manage the changes made by DML-statements.

• It also allows statements to be grouped together into logical transactions.

• Allow the user to manage database transactions.

• SQL provides commands to control the processing of these statements an indivisible unit of work.

• Are used mostly in the DML Commands in SQL

1. COMMIT

2. ROLLBACK

3. SAVEPOINT

4. AUTOCOMMIT

The TCL commands consist of the below commands:

COMMIT

Used to permanently save any transaction into the database.

ROLLBACK

Used for restoring the database to the last committed state.

SAVEPOINT

Used to temporarily save a transaction so that we can roll back to that point (saved point) whenever required.

RELEASE SAVEPOINT

Remove any particular savepoint

Grouping Results (GROUP BY Clause)

It groups the data from the SELECT table(s) and produces a single summary row for each group.

Integrity Constraint

Are used to ensure accuracy and consistency of data in a relational database.

• Primary key Constraints

• Unique Constraints

• Foreign Key Constraints

• Not Null Constraints

• Check Constraints

• Default

What are the types of integrity constraints?

Normalization

It is a process of reducing data redundancy in a database.

1. First Normal Form (1NF)

2. Second Normal Form (2NF)

3. Third Normal Form (3NF)

The different levels of normal forms:

• Better overall database organization

• Reduction of redundant data

• Data consistency within the database

• Flexible database design

• Efficient database security handling

• Enforces concept of referential integrity

What are the advantages of normalization?

Database Language

• Allows you to create database and table structures, perform basic data management chores (add, delete, and modify), and perform complex queries designed to transform the raw data into useful information.

• Moreover, a database language must perform such basic functions with minimal user effort, and its command structure and syntax must be easy to learn.