Module_1

Introduction to Database Concepts

​

Ms. Khushbu Tikhe

Assistant Professor

Department of Electronics and Computer Science

SLRTCE, Mira Road

What is Database

• The database is a collection of inter-related data which is used to retrieve, insert and delete the data efficiently. It is also used to organize the data in the form of a table, schema, views, and reports, etc.

​

• For example: The college Database organizes the data about the admin, staff, students and faculty etc.

​

• Using the database, you can easily retrieve, insert, and delete the information.

​

Database Management System

• Database management system is a software which is used to manage the database.
• For example: MySQL, Oracle, etc are a very popular commercial database which is used in different applications.
• DBMS provides an interface to perform various operations like database creation, storing data in it, updating data, creating a table in the database and a lot more.
• It provides protection and security to the database. In the case of multiple users, it also maintains data consistency.

Database Management System

DBMS allows users the following tasks:

• Data Definition: It is used for creation, modification, and removal of definition that defines the organization of data in the database.
• Data Updation: It is used for the insertion, modification, and deletion of the actual data in the database.
• Data Retrieval: It is used to retrieve the data from the database which can be used by applications for various purposes.
• User Administration: It is used for registering and monitoring users, maintain data integrity, enforcing data security, dealing with concurrency control, monitoring performance and recovering information corrupted by unexpected failure.

Characteristics of DBMS

• it uses a digital repository established on a server to store and manage the information.
• It can provide a clear and logical view of the process that manipulates data.
• DBMS contains automatic backup and recovery procedures.
• It contains ACID properties which maintain data in a healthy state in case of failure.
• It can reduce the complex relationship between data.
• It is used to support manipulation and processing of data.
• It is used to provide security of data.
• It can view the database from different viewpoints according to the requirements of the user.

Advantages of DBMS

• Controls database redundancy: It can control data redundancy because it stores all the data in one single database file and that recorded data is placed in the database.
• Data sharing: In DBMS, the authorized users of an organization can share the data among multiple users.
• Easily Maintenance: It can be easily maintainable due to the centralized nature of the database system.
• Reduce time: It reduces development time and maintenance need.
• Backup: It provides backup and recovery subsystems which create automatic backup of data from hardware and software failures and restores the data if required.
• Multiple user interface: It provides different types of user interfaces like graphical user interfaces, application program interfaces

Disadvantages of DBMS

• Cost of Hardware and Software: It requires a high speed of data processor and large memory size to run DBMS software.
• Size: It occupies a large space of disks and large memory to run them efficiently.
• Complexity: Database system creates additional complexity and requirements.
• Higher impact of failure: Failure is highly impacted the database because in most of the organization, all the data stored in a single database and if the database is damaged due to electric failure or database corruption then the data may be lost forever.

Different Feature/Characteristics of databases

• Managing information means taking care of it so that it works for us and is useful for the tasks we perform
• Managing information using a database allows us to become strategic users of the data we have.
• Because of the versatility of databases, we find them powering all sorts of projects.

​

A database can be linked to:

• A website that is capturing registered users
• A medical record system for a health care facility
• Your personal address book in your email client
• A collection of word-processed documents
• A system that issues airline reservations

Characteristics and Benefits of a Database

• Self-describing nature of a database system
• Enforcement of integrity constraints
• Data independence
• Provision for multiple views of data
• Backup and recovery facilities
• Restriction of unauthorized access(Data Securiy)
• Data sharing
• Control of data redundancy
• Sharing of data and multiuser system
• Support for multiple views of data

Different types of Database Users

• Database users are categorized based up on their interaction with the data base.

1. Database Administrator (DBA) :

• Database Administrator (DBA) is a person/team who defines the schema and also controls the 3 levels of database.
• The DBA will then create a new account id and password for the user if he/she need to access the data base.
• DBA is also responsible for providing security to the data base and he allows only the authorized users to access/modify the data base.
• DBA also monitors the recovery and back up and provide technical support.
• The DBA has a DBA account in the DBMS which called a system or superuser account.
• DBA repairs damage caused due to hardware and/or software failures.

Different types of Database Users

2. Naive / Parametric End Users :

Parametric End Users are the unsophisticated who don’t have any DBMS knowledge but they frequently use the data base applications in their daily life to get the desired results.

For examples, Railway’s ticket booking users are naive users. Clerks in any bank is a naive user because they don’t have any DBMS knowledge but they still use the database and perform their given task.

​

3. System Analyst :

System Analyst is a user who analyzes the requirements of parametric end users. They check whether all the requirements of end users are satisfied.

​

Different types of Database Users

4. Sophisticated Users :

Sophisticated users can be engineers, scientists, business analyst, who are familiar with the database.

They can develop their own data base applications according to their requirement.

They don’t write the program code but they interact the data base by writing SQL queries directly through the query processor.

​

5. Application Program :

Application Program are the back end programmers who writes the code for the application programs.

They are the computer professionals. These programs could be written in Programming languages such as Visual Basic, Developer, C, FORTRAN, COBOL etc.

What is File System?

• A File Management system is a DBMS that allows acces to single files or tables at a time.
• In a File System, data is directly stored in set of files. It contains flat files that have no relation to other files (when only one table is stored in single file, then this file is known as flat file).
• File system is basically a way of arranging the files in a storage medium like hard disk.
• File system organizes the files and helps in retrieval of files when they are required. File systems consists of different files which are grouped into directories.
• The directories further contain other folders and files. File system performs basic operations like management, file naming, giving access rules etc.
• Example

NTFS(New Technology File System),

EXT(Extended File System).

DBMS(Database Management System) :

• Database Management System is basically a software that manages the collection of related data.
• It is used for storing data and retrieving the data effectively when it is needed.
• It also provides proper security measures for protecting the data from unauthorized access.
• In Database Management System the data can be fetched by SQL queries and relational algebra. It also provides mechanisms for data recovery and data backup.
• Example:

Oracle, MySQL

Difference between File System and DBMS

File system

1. File system is a software that manages ad organizes the files in a storage medium within a computer.

2. Redundant data can be present in a file system.

3. It doesn’t provide backup and recovery of data if it is lost.

4. There is no efficient query processing in file system.

5. There is less data consistency in file system.

DBMS

1.DBMS is a software for managing the database.

​

2. In DBMS there is no redundant data.

​

3. It provides backup and recovery of data even if it is lost.

4. Efficient query processing is there in DBMS.

5. There is more data consistency because of the process of normalization.

Difference between File System and DBMS :

File system

6. It is less complex as compared to DBMS

​

7. File systems provide less security in comparison to DBMS.

​

8. It is less expensive than DBMS.

DBMS

6. It has more complexity in handling as compared to file system.

7. DBMS has more security mechanisms as compared to file system.

8. It has a comparatively higher cost than a file system.

Data independence

• Data independence means a change of data at one level should not affect another level.
• Two types of data independence are present in this architecture:
• Physical Data Independence: Any change in the physical location of tables and indexes should not affect the conceptual level or external view of data. This data independence is easy to achieve and implemented by most of the DBMS.
• Conceptual Data Independence: The data at conceptual level schema and external level schema must be independent. This means a change in conceptual schema should not affect external schema.

e.g.; Adding or deleting attributes of a table should not affect the user’s view of the table.

But this type of independence is difficult to achieve as compared to physical data independence because the changes in conceptual schema are reflected in the user’s view.

Roles of a Database administrator's (DBA)

• System Administrator / Designer
• Manage DBMS S/W and Server
• Resposible for deciding Storage and access method
• Data field update
• Adding new field into Database
• Database Developer/ Programmer

Responsibilities of a Database administrator's (DBA)

• A database administrator's (DBA) primary job is to ensure that data is available, protected from loss and corruption, and easily accessible as needed.
• Below are some of the chief responsibilities that make up the day-to-day work of a DBA.

1. Software installation and Maintenance

2. Data Extraction, Transformation, and Loading

3. Specialised Data Handling

Today’s databases can be massive and may contain unstructured data types such as images, documents, or sound and video files. Managing a very large database (VLDB) may require higher-level skills and additional monitoring and tuning to maintain efficiency.

4. Database Backup and Recovery

5. Security

A DBA needs to know potential weaknesses of the database software and the company’s overall system and work to minimise risks. No system is one hundred per cent immune to attacks, but implementing best practices can minimise risks.

​

6. Authentication

Setting up employee access is an important aspect of database security. DBAs control who has access and what type of access they are allowed. For instance, a user may have permission to see only certain pieces of information, or they may be denied the ability to make changes to the system.

7. Capacity Planning

The DBA needs to know how large the database currently is and how fast it is growing in order to make predictions about future needs. Storage refers to how much room the database takes up in server and backup space. Capacity refers to usage level.

If the company is growing quickly and adding many new users, the DBA will have to create the capacity to handle the extra workload.

8. Performance Monitoring

Monitoring databases for performance issues is part of the on-going system maintenance a DBA performs. If some part of the system is slowing down processing, the DBA may need to make configuration changes to the software or add additional hardware capacity.

9. Database Tuning

Performance monitoring shows where the database should be weaked to operate as efficiently as possible. The physical configuration, the way the database is indexed, and how queries are handled can all have a dramatic effect on database performance.

With effective monitoring, it is possible to proactively tune a system based on application and usage instead of waiting until a problem develops.

10. Troubleshooting

DBAs are on call for troubleshooting in case of any problems. Whether they need to quickly restore lost data or correct an issue to minimise damage, a DBA needs to quickly understand and respond to problems when they occur.

Skill Required for A DBA

DBMS Architecture

Components of a database system

Query processor: The query processor is important because it helps the database system simplify and facilitate access to data.

​

Storage manager: storage manager is important because databases typically require a large amount of storage space.

​

• Transaction Manager

Query Processor components

• Query Processor will accept query from user and solve it by accessing the database
• DDL interpreter: which interprets DDL statements and records the definitions in the data dictionary.

Data Definition Language (DDL) is a stands for commands that define the different structures in a database. EX-CREAT, DROP, RENAME

• DML compiler:

which translates DML statements in a query language into low-level instructions that the query evaluation engine understands.

A query can usually be translated into any of a number of alternative evaluation plans that all give the same result.

The DML compiler also performs query optimization, that is, it picks the lowest cost evaluation plan from among the alternatives.

• A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.
• Query evaluation engine: which executes low-level instructions generated by the DML compiler.

Storage Manager

• A storage manager is a program module that provides the interface between the lowlevel data stored in the database and the application programs and queries submitted to the system
• The storage manager is responsible for the interaction with the file manager.
• The raw data are stored on the disk using the file system, which is usually provided by a conventional operating system.
• The storage manager translates the various DML statements into low-level file-system commands.
• Thus, the storage manager is responsible for storing, retrieving, and updating data in the database.

Storage manager components

• Authorization and integrity manager: which tests for the satisfaction of integrity constraints and checks the authority of users to access data.

​

• Transaction manager: which ensures that the database remains in a consistent (correct) state despite system failures, and that concurrent transaction executions proceed without conflicting.

​

• File manager: which manages the allocation of space on disk storage and the data structures used to represent information stored on disk.

​

• Buffer manager: which is responsible for fetching data from disk storage into main memory, and deciding what data to cache in main memory.

The buffer manager is a critical part of the database system, since it enables the database to handle data sizes that are much larger than the size of main memory.

Data structures implementation by storage manager

The storage manager implements several data structures as part of the physical system implementations:

​

Data files: These are files in the physical memory used to store the database itself

​

Data Dictionary: Data dictionary stores the metadata (data about data) that provides the information about the definitions of the data items and their relationships, authorizations, and usage statistics.

In addition, any changes made to the physical structure of the database are automatically recorded in the data dictionary

​

Indices: Indices are used to provide faster access to data items stored in the physical storage

Transaction Manager

• A transaction is a series of small database operations that together form a single large operations.
• A transaction is started by issuing a BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION command. once this command executed the DBMS start monitoring the transition.
• Transaction management compnent will ensure the both atomicity and durability properties
• Transaction - manager ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures.

Exercises

1. What is a database management system (DBMS)?

2. What are the properties of a DBMS?

3. Provide three examples of a real-world database (e.g., the library contains a database of books).

4. Discuss Different types of Database Users

5. Explain the Feature of DBMS.

6. what is Difference between File System and DBMS?

7. Define Data Indepandence and Exlain it types.

8. Write Shorte Note on : a. DBA

b. Roles of DBA

c. Responsibilities of DBA

d. Skill Required for DBA

9. Describe overall achitecture of DBMS with Diagram.

Reference

• https://opentextbc.ca/dbdesign01/chapter/chapter-3-characteristics-and-benefits-of-a-database/

​

• https://www.geeksforgeeks.org/introduction-of-3-tier-architecture-in-dbms-set-2/

​

• https://www.javatpoint.com/dbms-architecture

​

• https://sites.google.com/site/aishshub/database-management-systems