database
DESCRIPTION
TRANSCRIPT
DATABASE DESIGN
SUBMITTED BY :SURBHI JAGGINEELESH VAISH
A field is the physical implementation of a data attribute. They are the smallest unit of meaningful data.
Fields
A primary key is a field whose values identify one and only one record in a file.A secondary key is an alternate identifier for a record.A foreign key is a pointer to a record in a different file.
A descriptive field is any other (nonkey) field that stores business data.
3
A record is a collection of fields arranged in a predefined format.
Fixed-length record structuresVariable-length record structures
A blocking factor is the number of logical records included in a single read or write operation (from the computer’s perspective).
Records
DBMS Environment
SkillsInventory
ProfitSharing
Inventory
EmployeeBenefits
Application
DBMS • Skills Data•Profit Sharing Data•Benefits Data
5
Controlled RedundancyEase of learning and useData IndependenceMore Information at Low CostAccuracy and IntegrityRecovery From FailurePrivacy and securityPerformance
Objectives Of Database
6
Users View
Processing
Data Model
Data File
Key Terms
7
“ In a Database Environment, a DBMS is the software that provides the
interface between the data file on disk
and the program that requests
processing. ”
What is DBMS?
8
DML – or ‘Data Manipulation Language’DML manipulates data, i.e. it specifies what is
required
DDL – or ‘Data Definition Language’DDL describes how the data is structured
Some Important Terms
9
Storing, Retrieving and Updating dataCreating program and data independenceEnforcing Procedures for data integrityReducing data redundancyProviding security facilities for defining
users and enforcing authorizationsReducing Physical storage requirements
by separating the logical and physical aspects of the database.
Functions of a DBMS
LOGICAL AND PHYSICAL VIEWS OF DATA
11
It is the way data exist in physical storage.
It deals with how data stored, accessed, or related to other data in storage.
PHYSICAL VIEW OF DATA
12
what the data look like, regardless of how they are stored.
The logical views are the user’s view, the programmer’s view, and the overall logical view, called a schema.
LOGICAL VIEW OF DATA
13
It is the view that helps the DBMS (database management system) decide what data in storage it should act upon as requested by the application program.
Example: Is the arrival and departure display at an airport.
SCHEMAS
16
It is the programmer’s (pilot’s) view.Many subschemas can be derived
from one schema.Example: just as different pilots
visualize different views of landing approach, although all (it is hoped) arrive at the sheduled time indicated on the CRT screen display (schema).
SUBSCHEMAS
AN ENTITY IS A THING OR OBJECT OF IMPORTANCE ABOUT WHICH DATA MUST BE CAPTURED. ALL THINGS AREN'T ENTITIES—ONLY THOSE ABOUT WHICH INFORMATION SHOULD BE CAPTURED. AN ENTITY IS SOMETHING THAT EXISTS INDEPENDENTLY .
What is an ENTITY ??
Cardinality
22
Database normalization is a design technique by which relational database systems are structured in such a way as to make them less vulnerable to certain types of logical inconsistencies and anomalies.
Tables can be normalized to varying degrees: relational database theory defines "normal forms" of successively higher degrees of stringency.
DATABASE NORMALIZATION
23
An logical entity (or physical table) is in first normal form if there are no attributes (fields) that can have more than one value for a single instance (record).
An logical entity (or physical table) is in second normal form if it is already in first normal form and if the values of all nonprimary key attributes are dependent on the full primary key.
An logical entity (or physical table) is in third normal form if it is already in second normal form and if the values of all nonprimary key attributes are not dependent on other nonprimary key attributes .
Data Normalization
24
The criteria for first normal form :A table must be guaranteed not to have duplicate records.
There must not be any repeating groups, ie, no attributes which occur a different number of times on different records.
First Normal Form
R NO. NAME DOB SUBJECT GRADE
1 ABC 2 MARCH,1989 OSBSCONA
AA+B+
2 DEF 25 DECEMBER,92
BSENGLISH
AA
R NO. NAME DOB SUBJECT GRADE
1 ABC 2 MARCH,1989 OS A
1 ABC 2 MARCH,1989 BS A+
1 ABC 2 MARCH,1989 CONA B+
2 DEF 25 DECEMBER,92
BS A
2 DEF 25 DECEMBER,92
ENGLISH A
26
Criteria for second normal form :The table must be in 1NFNone of the non-prime attributes of the table are functionally dependant on a part of a candidate key.
None of a 1NF’s candidate keys are composite.
Second Normal Form
R NO. NAME DOB
1 ABC 2 MARCH,1989
2 DEF 25 DECEMBER,92
R NO. SUBJECT GRADE
1 OS A
1 BS A+
1 CONA B+
2 BS A
2 ENGLISH A
28
Criteria for third normal form:Table must be in 2NF.There are no non-trivial functional dependencies between two non prime attributes.
Third Normal Form
SSN PNUMBER HOURS ENAME PNAME PLOCATION
SSN PNUMBER
HOURS
PNUMBER PNAME PLOCATION
SSN ENAME
30
Managing data activitiesManaging database structure
Managing DBMS
Roles of Database Administrator