Database Management Systems
IMP QUESTIONS :
Difference between file system and database
Database architecture
Scheme architecture
Database users
In unit-2
Queries
String functions
Number & date function s
In unit 3
Steps to follow design of e-r diagram
E-r diagram for any University database or airline or hospital management
1.)Difference between File System and DBMS:
2)
DBMS Architecture
The DBMS design depends upon its architecture. The basic client/server architecture is used to deal with a large number of PCs, web servers, database servers and other components that are connected with networks.
The client/server architecture consists of many PCs and a workstation which are connected via the network.
DBMS architecture depends upon how users are connected to the database to get their request done.
Types of DBMS Architecture
Database architecture can be seen as a single tier or multi-tier. But logically, database architecture is of two types like: 2-tier architecture and 3-tier architecture.
1-Tier Architecture
In this architecture, the database is directly available to the user. It means the user can directly sit on the DBMS and uses it.
Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for end users.
The 1-Tier architecture is used for development of the local application, where programmers can directly communicate with the database for the quick response.
2-Tier Architecture
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.
The user interfaces and application programs are run on the client-side.
The server side is responsible to provide the functionalities like: query processing and transaction management.
To communicate with the DBMS, client-side application establishes a connection with the server side.
3-Tier Architecture
The 3-Tier architecture contains another layer between the client and server. In this architecture, client can't directly communicate with the server.
The application on the client-end interacts with an application server which further communicates with the database system.
End user has no idea about the existence of the database beyond the application server. The database also has no idea about any other user beyond the application.
The 3-Tier architecture is used in case of large web application.
3)
Three schema Architecture
The three schema architecture is also called ANSI/SPARC architecture or three-level architecture.
This framework is used to describe the structure of a specific database system.
The three schema architecture is also used to separate the user applications and physical database.
The three schema architecture contains three-levels. It breaks the database down into three different categories.
The three-schema architecture is as follows:
In the above diagram:
It shows the DBMS architecture.
Mapping is used to transform the request and response between various database levels of architecture.
Mapping is not good for small DBMS because it takes more time.
In External / Conceptual mapping, it is necessary to transform the request from external level to conceptual schema.
In Conceptual / Internal mapping, DBMS transform the request from the conceptual to internal level.
Objectives of Three schema Architecture
The main objective of three level architecture is to enable multiple users to access the same data with a personalized view while storing the underlying data only once. Thus it separates the user's view from the physical structure of the database. This separation is desirable for the following reasons:
Different users need different views of the same data.
The approach in which a particular user needs to see the data may change over time.
The users of the database should not worry about the physical implementation and internal workings of the database such as data compression and encryption techniques, hashing, optimization of the internal structures etc.
All users should be able to access the same data according to their requirements.
DBA should be able to change the conceptual structure of the database without affecting the user's
Internal structure of the database should be unaffected by changes to physical aspects of the storage.
1. Internal Level
The internal level has an internal schema which describes the physical storage structure of the database.
The internal schema is also known as a physical schema.
It uses the physical data model. It is used to define that how the data will be stored in a block.
The physical level is used to describe complex low-level data structures in detail.
The internal level is generally is concerned with the following activities:
Storage space allocations.
For Example: B-Trees, Hashing etc.Access paths.
For Example: Specification of primary and secondary keys, indexes, pointers and sequencing.Data compression and encryption techniques.
Optimization of internal structures.
Representation of stored fields.
2. Conceptual Level
The conceptual schema describes the design of a database at the conceptual level. Conceptual level is also known as logical level.
The conceptual schema describes the structure of the whole database.
The conceptual level describes what data are to be stored in the database and also describes what relationship exists among those data.
In the conceptual level, internal details such as an implementation of the data structure are hidden.
Programmers and database administrators work at this level.
3. External Level
At the external level, a database contains several schemas that sometimes called as subschema. The subschema is used to describe the different view of the database.
An external schema is also known as view schema.
Each view schema describes the database part that a particular user group is interested and hides the remaining database from that user group.
The view schema describes the end user interaction with database systems.
Mapping between Views
The three levels of DBMS architecture don't exist independently of each other. There must be correspondence between the three levels i.e. how they actually correspond with each other. DBMS is responsible for correspondence between the three types of schema. This correspondence is called Mapping.
There are basically two types of mapping in the database architecture:
Conceptual/ Internal Mapping
External / Conceptual Mapping
Conceptual/ Internal Mapping
The Conceptual/ Internal Mapping lies between the conceptual level and the internal level. Its role is to define the correspondence between the records and fields of the conceptual level and files and data structures of the internal level.
External/ Conceptual Mapping
The external/Conceptual Mapping lies between the external level and the Conceptual level. Its role is to define the correspondence between a particular external and the conceptual view.
4 question)
Database users are categorized based up on their interaction with the data base.
These are seven types of data base users in DBMS.
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.
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.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.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.Data Base Designers :
Data Base Designers are the users who design the structure of data base which includes tables, indexes, views, constraints, triggers, stored procedures. He/she controls what data must be stored and how the data items to be related.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.Casual Users / Temporary Users :
Casual Users are the users who occasionally use/access the data base but each time when they access the data base they require the new information, for example, Middle or higher level manager
UNIT-2
1.NumberAndDateFunctions
NUMERIC(OR)NUMBER(OR)ARITHMETIC
FUNCTIONS:
These functions areusedwithnumeric
data.Theargumenttothefunctionisanumber.
1.ABS:
USAGE:itreturnsabsolutevalueofthegiven
number.
SYNTAX:ABS(n)
EXAMPLE:selectABS(-10)fromdual;
o/p:10
2.CEIL:-
USAGE:-Itreturnsthesmallestintegergreater
thanthegivennumber.
SYN:-CEIL(n)
EX:-selectCEIL(123.456)fromdual;
o/p:124
3.FLOOR:-
USAGE:-Itreturnsthelargestintegersmaller
thanthegivennumber.
SYN:-FLOOR(n)
EX:-selectfloor(123.456)fromdual;
o/p:123
4.LN:-
USAGE:-Itreturnsthenaturallogarithmofthe
givennumber
SYN:-LN(n)
EX:-selectLN(10)fromdual; o/p:
2.30258509
5.LOG:-
USAGE:-Itreturnsthelogarithmofnwithbase
m
SYN:-LOG(m,n)
EX:-selectLOG(100,10)fromdual;
O/P:0.5
6.MOD:-
USAGE:-Itreturnstheremainderofmdividedby
n;
SYN:-MOD(m.n)
EX:-selectMOD(10,3)fromdual; o/p:
1
7.POWER:-
USAGE:-Ititreturnsvalueequaltomraisedbyn;
SYN:-POWER(m,n)
EX:-selectPOWER(10,2)fromdual;
o/p:100
8.ROUND:-
USAGE:-Itreturnsthefloatvaluenumberrounds
mtontheplacesafterdecimal
SYN:-ROUND(m,n)
EX:-selectROUND(1.23456,3)fromdual;
o/p:1.235
9.SQRT:-
USAGE:-Itcalculatesthesquarerootvalueof
numbern
SYN:-SQRT(n)
EX:-selectSQRT(9)fromdual;
o/p:3
10.TRUNC:-
USAGE:-Ittruncatesgivenfloatedvalued
numbermtonplacesafterdecimal.
SYN:-TRUNC(m,n)
EX:-selectTRUNC(1.23456,3)fromdual;
o/p:1.234
11.SIGN:-
USAGE:-Itreturns1ifn>0, -1ifn<0, 0ifn=0
SYN:-SIGN(n)
EX:-selectSIGN(-5)fromdual; o/p:
-1
DATEFUNCTIONS:-
Thesefunctionsareusedwithdates
ADDMONTHS:-
USAGE:-itaddsnmonthstothespecifieddate
SYN:-ADD_MONTHS(date,n)
EX:-selectADD_MONTHS(‘1-jan-05’
,5)fromdual;
o/p:-01-jun-05
LASTDAY:-
USAGE:-Itgiveslastdateofspecifiedmonth.
SYN:-last_day(d)
EX:-selectlast_day(‘1-jan-05’)fromdual;
o/p:-31-jan-05
MONTHSBETWEEN:-
USAGE:-Itgivesno.ofmonthsbetweendate1
anddate2
SYN:-MONTHS_BETWEEN(date1,date2)
EX:-selectMONTHS_BETWEEN(’17-may-
2012’
,
’19-jul-09’)fromdual; o/p:-34
NEXTDAY:-
USAGE:-itgivesdateofthenextoccurrenceof
thespecifiedday
SYN:-NEXT_DAY(date,day)
EX:-selectNEXT_DAY(‘1-jan-05’
,
’fri’)fromdual;
o/p:-07-jan-05
EXTRACT:-
USAGE:-itisusedtoextracttimecomponent
fromdateexpression
SYN:-EXTRACT(FMTfromdate)
EX:-selectEXTRACT(yearfromdate‘2003-08-
02’)fromdual; o/p:-2003
2.StringFunctions
Thesefunctionsoperateoncharacterdata
1.ASCII:-
USAGE:-ASCIIvalueofthefirstcharacter.
SYN:-ASCII(char)
EX:-selectASCII(`ABC’)fromdual; o/p:
65
2.CHR:-
USAGE:-ItreturnsthecharacterwithASCIIvalue
n
SYN:-CHR(char)
EX:-selectCHR(`65’)fromdual; o/p:
A
3.UPPER:-
USAGE:-itconvertsalllettersingivenstringinto
uppercase
SYN:-UPPER(str)
EX:-selectUPPER(`poly’)fromdual;
o/p:POLY
4.LOWER:-
USAGE:-itconvertsalllettersingivenstringinto
lowercase
SYN:-LOWER(str)
EX:-selectLOWER(`poly’)fromdual;
o/p:poly
5.INITCAP:-
USAGE:-itconvertsfirstletterineverywordinto
upperandothersintolower
SYN:-INITCAP(str)
EX:-selectINITCAP(`raMU’)fromdual;
o/p:Ramu
6.LENGTH:-
USAGE:-itreturnstheno.ofcharactersinthe
givenstringincludingwhitespaces.
SYN:-LENGTH(str)
EX:-selectLENGTH(`ramana’)fromdual;
o/p:6
7.SUBSTR:-
USAGE:-itwillextractncharacterfromthe
givenstringstartingfrommthposition.
SYN:-substr(str,m,n)
EX:-selectSUBSTR(`ramanakumar’
,7,5)from
dual; o/p:Kumar
8.INSTR:-
USAGE:-Itdisplaysthelocationofstrinthe
givenstring
SYN:-INSTR(string,str)
EX:-selectINSTR(`tryingtokeepthethingsas
simpleaspossible’
,`as’)fromdual; o/p:-27
9.INSTR:-
USAGE:-itdisplaysnthoccurrenceofstrinthe
stringstartingfromm
SYN:-INSTR(string,str,m,n)
EX:-selectINSTR(`tryingtokeepthethingsas
simpleaspossible’
,`as’
,1,2)fromdual; o/p:
-37
10.LPAD:-
USAGE:-thisfunctionisusedtoleftpadthe
givenstringwithspecifiedcharacter
SYN:-LPAD(char1,m,char2)
EX:-selectLPAD(`BCD’
,4,`A’)fromdual;
o/p:-ABCD
11.RPAD:-
USAGE:-thisfunctionisusedtorightpadthe
givenstringwithspecifiedcharacter
SYN:-RPAD(char1,m,char2)
EX:-selectRPAD(`BILL’
,12,`CLINTON’)fromdual;
o/p:-BILLCLINTON
12.LTRIM:-
USAGE:-thisfunctionremovesthestringfrom
thegivenstringifitistheretotheleftgiven
string
SYN:-LTRM(char,str)
EX:-selectLTRM(`Georgebush’
,`george’)from
dual; o/p:-bush
13.RTRIM:-
USAGE:-thisfunctionremovesthestringfrom
thegivenstringifitistheretotherightgiven
string
SYN:-RTRM(char,str)
EX:-selectRTRM(`charlessbabbage’
,`babbage’)
fromdual; o/p:-charless
14.TRANSLATE:-
USAGE:-Ittranslatescharactersetinfrom
charactertocharactersetntoc
SYN:-translate(char,fromc,toc)
EX:-selecttranslate(`BOMBAY’
,`M’
,`A’)from
dual; o/p:-BOABAY
UNIT-3
E-R DIAGRAM FOR hospital management
E- R diagram for airline
