ubpALnLj

1A database is a collection of related data which represents some aspect of the real world. A database system is designed to be built and populated with data for a certain task.
2
3Database Management System (DBMS) is a software for storing and retrieving users' data while considering appropriate security measures. It consists of a group of programs which manipulate the database. The DBMS accepts the request for data from an application and instructs the operating system to provide the specific data. In large systems, a DBMS helps users and other third-party software to store and retrieve data. 
4
5Advantages of DBMS
6
7    DBMS offers a variety of techniques to store & retrieve data
8    DBMS serves as an efficient handler to balance the needs of multiple applications using the same data
9    Uniform administration procedures for data
10    Application programmers never exposed to details of data representation and storage.
11    A DBMS uses various powerful functions to store and retrieve data efficiently.
12    Offers Data Integrity and Security
13    The DBMS implies integrity constraints to get a high level of protection against prohibited access to data.
14    A DBMS schedules concurrent access to the data in such a manner that only one user can access the same data at a time
15    Reduced Application Development Time
16
17Disadvantage of DBMS
18
19DBMS may offer plenty of advantages but, it has certain flaws-
20
21    Cost of Hardware and Software of a DBMS is quite high which increases the budget of your organization.
22    Most database management systems are often complex systems, so the training for users to use the DBMS is required.
23    In some organizations, all data is integrated into a single database which can be damaged because of electric failure or     database is corrupted on the storage media
24    Use of the same program at a time by many users sometimes lead to the loss of some data.
25    DBMS can't perform sophisticated calculations.
26
27A relationship is used to describe the relation between entities. Diamond or rhombus is used to represent the relationship.
28
29a. One-to-One Relationship
30
31When only one instance of an entity is associated with the relationship, then it is known as one to one relationship.
32For example, A female can marry to one male, and a male can marry to one female.
33
34b. One-to-many relationship
35
36When only one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then this is known as a one-to-many relationship.
37For example, Scientist can invent many inventions, but the invention is done by the only specific scientist.
38
39c. Many-to-one relationship
40
41When more than one instance of the entity on the left, and only one instance of an entity on the right associates with the relationship then it is known as a many-to-one relationship.
42
43For example, Student enrolls for only one course, but a course can have many students.
44
45d. Many-to-many relationship
46
47When more than one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then it is known as a many-to-many relationship.
48
49For example, Employee can assign by many projects and project can have many employees. 
50
51
52If a database design is not perfect, it may contain anomalies, which are like a bad dream for any database administrator. Managing a database with anomalies is next to impossible.
53
54Normalization is a method to remove all these anomalies and bring the database to a consistent state.
55
56
57
58
59    The 2-Tier architecture is same as basic client-server. In the two-tier architecture, applications on the client end can directly communicate with the database at the server side. For this interaction, API's like: ODBC, JDBC are used.
60    The user interfaces and application programs are run on the client-side.
61    The server side is responsible to provide the functionalities like: query processing and transaction management.
62    To communicate with the DBMS, client-side application establishes a connection with the server side.
63
64
65
66Microsoft Access is a Database Management System offered by Microsoft. It uses the Microsoft Jet Database Engine and comes as a part of the Microsoft Office suite of application.
67
68Microsoft Access offers the functionality of a database and the programming capabilities to create easy to navigate screens (forms). 
69
70
71Create a new table in a new database
72
73    Click File > New, and then select Blank desktop database.
74
75    In the File Name box, type a file name for the new database.
76
77    To browse to a different location and save the database, click the folder icon.
78
79    Click Create.
80
81    The new database opens, and a new table named Table1 is created and opens in Datasheet view.
82
83Create a new table in an existing database
84
85    Click File > Open, and click the database if it is listed under Recent. If not, select one of the browse options to locate the database.
86
87    In the Open dialog box, select the database that you want to open, and then click Open.
88
89    On the Create tab, in the Tables group, click Table.
90
91    A new table is inserted in the database and the table opens in Datasheet view.
92
93
94
95
96
97
98
99
100
101
102
103
104
105    DDL stands for Data Definition Language. It is used to define database structure or pattern.
106    It is used to create schema, tables, indexes, constraints, etc. in the database.
107    Using the DDL statements, you can create the skeleton of the database.
108    Data definition language is used to store the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc.
109
110Here are some tasks that come under DDL:
111
112    Create: It is used to create objects in the database.
113    Alter: It is used to alter the structure of the database.
114    Drop: It is used to delete objects from the database.
115    Truncate: It is used to remove all records from a table.
116    Rename: It is used to rename an object.
117    Comment: It is used to comment on the data dictionary.
118
119These commands are used to update the database schema that's why they come under Data definition language. 
120
121
122Data Manipulation Language
123
124DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a database. It handles user requests.
125
126Here are some tasks that come under DML:
127
128    Select: It is used to retrieve data from a database.
129    Insert: It is used to insert data into a table.
130    Update: It is used to update existing data within a table.
131    Delete: It is used to delete all records from a table.
132    Merge: It performs UPSERT operation, i.e., insert or update operations.
133    Call: It is used to call a structured query language or a Java subprogram.
134    Explain Plan: It has the parameter of explaining data.
135    Lock Table: It controls concurrency.
136
137Data Control Language
138
139    DCL stands for Data Control Language. It is used to retrieve the stored or saved data.
140    The DCL execution is transactional. It also has rollback parameters.
141
142    (But in Oracle database, the execution of data control language does not have the feature of rolling back.)
143
144Here are some tasks that come under DCL:
145
146    Grant: It is used to give user access privileges to a database.
147    Revoke: It is used to take back permissions from the user.
148
149There are the following operations which have the authorization of Revoke:
150
151CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT.
152
153
154
155
156What are ACID Properties?
157
158For maintaining the integrity of data, the DBMS system you have to ensure ACID properties. ACID stands for Atomicity, Consistency, Isolation, and Durability.
159
160    Atomicity: A transaction is a single unit of operation. You either execute it entirely or do not execute it at all. There cannot be partial execution.
161    Consistency: Once the transaction is executed, it should move from one consistent state to another.
162    Isolation: Transaction should be executed in isolation from other transactions (no Locks). During concurrent transaction execution, intermediate transaction results from simultaneously executed transactions should not be made available to each other. (Level 0,1,2,3)
163    Durability: · After successful completion of a transaction, the changes in the database should persist. Even in the case of system failures.
164
165
166DBMS is a highly complex system with hundreds of transactions being executed every second. The durability and robustness of a DBMS depends on its complex architecture and its underlying hardware and system software. If it fails or crashes amid transactions, it is expected that the system would follow some sort of algorithm or techniques to recover lost data.
167
168Log-based Recovery
169
170Log is a sequence of records, which maintains the records of actions performed by a transaction. It is important that the logs are written prior to the actual modification and stored on a stable storage media, which is failsafe.
171
172Log-based recovery works as follows −
173
174    The log file is kept on a stable storage media.
175
176    When a transaction enters the system and starts execution, it writes a log about it.
177
178
179Recovery with Concurrent Transactions
180
181When more than one transaction are being executed in parallel, the logs are interleaved. At the time of recovery, it would become hard for the recovery system to backtrack all logs, and then start recovering. To ease this situation, most modern DBMS use the concept of 'checkpoints'.
182
183Physical: This is the lowest level of data abstraction. It tells us how the data is actually stored in memory. The access methods like sequential or random access and file organisation methods like B+ trees, hashing used for the same. Usability, size of memory, and the number of times the records are factors which we need to know while designing the database.
184Suppose we need to store the details of an employee. Blocks of storage and the amount of memory used for these purposes is kept hidden from the user.
185
186
187
188
189
190Logical: This level comprises of the information that is actually stored in the database in the form of tables. It also stores the relationship among the data entities in relatively simple structures. At this level, the information available to the user at the view level is unknown.
191We can store the various attributes of an employee and relationships, e.g. with the manager can also be stored.
192
193View: This is the highest level of abstraction. Only a part of the actual database is viewed by the users. This level exists to ease the accessibility of the database by an individual user. Users view data in the form of rows and columns. Tables and relations are used to store data. Multiple views of the same database may exist. Users can just view the data and interact with the database, storage and implementation details are hidden from them.