|
UNIT
– 1 |
||||||||||||||||||||||||||
|
TOPIC |
||||||||||||||||||||||||||
|
Unit-01/Lecture-01 |
||||||||||||||||||||||||||
|
BASICS
of DBMS a. Data: ·
It is a collection of raw facts. ·
The facts that can be recorded and which have implicit
meaning known as 'data'. ·
Example: Customer ----- 1.cname. 2.
cno. 3.
ccity. b. Database: ·
It is a collection of interrelated data. ·
These can be stored in the form of tables. ·
A database can be of any size and varying complexity. ·
A database may be generated and manipulated manually or it
may be computerized. ·
Example: Customer database consists the fields as
cname, cno, and ccity
c. Database System:
Database approach v/s
Traditional file accessing approach: DBMS(Data
Base Management System): ·
It is computerized system, whose overall purpose is to
maintain the information and to make that the information is available on
demand. ·
Advantages: 1. Redundancy can be
reduced. 2. Inconsistency can be
avoided. 3. Data can be shared. 4. Standards can be
enforced. 5. Security restrictions can
be applied. 6. Integrity can be
maintained. 7. Data gathering can be
possible. 8. Requirements can be
balanced. File
Processing System: *
It is a collection of interrelated data and programs to
access the data. *
It is a collection of programs that enables users to
create and maintain a database. *
It is a 'General Purpose Software System’ that facilitates
the processes of Defining, Constructing and Manipulating databases for
various applications. *
Advantages: 1.
Data Independence. 2.
Efficient Data Access. 3.
Data Integrity and security. 4.
Data administration. 5.
Concurrent access and Crash recovery. 6.
Reduced Application Development Time. Dis-advantages 1.
Data Redundancy. 2.
Data Inconsistency. 3.
Difficulty in accessing the data. 4.
Data Isolation. 5.
Security Problems. Database Architecture: •
The design of a DBMS depends on its architecture. It
can be centralized or decentralized or hierarchical. The architecture of a
DBMS can be seen as either single tier or multi-tier. •
N-tier architecture divides the whole system into
related but independent n modules, which can be independently
modified, altered, changed, or replaced. •
In 1-tier architecture, the DBMS is the only entity
where the user directly sits on the DBMS and uses it. Any changes done here
will directly be done on the DBMS itself. It does not provide handy tools for
end-users. Database designers and programmers normally prefer to use
single-tier architecture. •
If the architecture of DBMS is 2-tier, then it must
have an application through which the DBMS can be accessed. Programmers use
2-tier architecture where they access the DBMS by means of an application.
Here the application tier is entirely independent of the database in terms of
operation, design, and programming. •
3-tier Architecture: 3-tier
architecture separates its tiers from each other based on the complexity of
the users and how they use the data present in the database. It is the most
widely used architecture to design a DBMS. ·
Database (Data) Tier − at this tier, the database resides along
with its query processing languages. We also have the relations that define
the data and their constraints at this level. ·
Application (Middle) Tier − at this tier reside the application server
and the programs that access the database. For a user, this application tier
presents an abstracted view of the database. End-users are unaware of any
existence of the database beyond the application. At the other end, the
database tier is not aware of any other user beyond the application tier.
Hence, the application layer sits in the middle and acts as a mediator
between the end-user and the database. ·
User (Presentation) Tier − End-users operate on this tier and they know
nothing about any existence of the database beyond this layer. At this layer,
multiple views of the database can be provided by the application. All views
are generated by applications that reside in the application tier. Multiple-tier database architecture is highly
modifiable, as almost all its components are independent and can be changed
independently. References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
Unit-1/Lecture-02 |
||||||||
|
DATA MODELS •
A
collection of concepts that can be used to describe the structure of a
database (data types, relationships, and constraints) •
basic
operations (retrieval and updates) •
specify
the dynamic aspect or behavior of a database application( user-defined
operations ) ·
The entire structure of a database can be described using
a data model. ·
A data model is a collection of conceptual tools for
describing ·
Data models can be classified into following types. 1.
Object Based Logical Models. 2.
Record Based Logical Models. 3.
Physical Models. Explanation is as below. 1.Object Based Logical Models: ·
These models can be used in describing the data at the
logical and view levels. ·
These models are having flexible structuring capabilities
classified into following types. a.
The entity-relationship model. b.
The object-oriented model. c.
The semantic data model. d.
The functional data model. 2.Record
Based Logical Models: ·
These models can also be used in describing the data at
the logical and view levels. ·
These models can be used for both to specify the overall
logical structure of the database and a higher-level description. ·
These models can be classified into, 1. Relational
model. 2. Network model. 3.
Hierarchal model. 3.
Physical Models: ·
These models can be used in describing the data at the
lowest level, i.e. physical level. ·
These models can be classified into a.
Unifying model b.
Frame memory model. Schema and instances: Schema: •
Is the description of the database
(not database itself) – Specified during database design – Not expected
to change frequently – A displayed schema is called a schema diagram (Fig
2.1) •
Each object in the schema-such as
STUDENT or COURSE-is a schema construct. •
Schema diagram represents only
some aspects of a schema (name of record type, data element and some type of
constraint) Instances and database states: •
The
data in the database at a particular moment in time is called a database
state or snapshot or current set of occurrences or instances in
the database •
When
we define a new database we have database state is empty state
(schema specified only in DBMS) •
The
initial state when the database is first populated •
Then
At any point in time, the database has a current state •
schema
evolution: when we need to change the schema References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
|
Unit-01/Lecture-03 |
||||||||
LEVELS OF DATA ABSTRACTION
|
|
S.NO |
RGPV
QUESTIONS |
Year |
Marks |
|
Q.1 |
What is schema and subschema?
Explain these two concepts through examples. |
Dec
2014 |
3 |
|
Unit-01/Lecture-04 |
||||||||||||
|
Overall Database
Architecture: We saw how we can connect to database. But how is the database laid to
process all user requests? Since it is responsible to store huge amount of data
and is capable of handling multiple requests from users simultaneously, it
should be arranged properly. One can imagine a database as a brain! How is
the structure of brain? Bit sophisticated and each part of the brain is
responsible for some specific tasks. Similarly, Database is also designed. At very high level, a database is considered as shown in below
diagram. Let us see them in detail below. · Applications: - It can be considered as a user friendly web page where the user
enters the requests. Here he simply enters the details that he needs and
presses buttons to get the data. · End User: - They are the real users of the database. They can be developers,
designers, administrator or the actual users of the database. · DDL: - Data Definition Language (DDL) is a query fired to create
database, schema, tables, mappings etc in the database. These are the
commands used to create the objects like tables, indexes in the database for
the first time. In other words, they create structure of the database. · DDL Compiler: - This part of database is responsible for processing the DDL
commands. That means these compiler actually breaks down the command into
machine understandable codes. It is also responsible for storing the metadata
information like table name, space used by it, number of columns in it,
mapping information etc. · DML Compiler: - When the user inserts, deletes, updates or retrieves the record
from the database, he will be sending request which he understands by
pressing some buttons. But for the database to work/understand the request,
it should be broken down to object code. This is done by this compiler. One
can imagine this as when a person is asked some question, how this is broken
down into waves to reach the brain! · Query Optimizer: - When user fires some request, he is
least bothered how it will be fired on the database. He is not all aware of
database or its way of performance. But whatever be the request, it should be
efficient enough to fetch, insert, update or delete the data from the
database. The query optimizer decides the best way to execute the user
request which is received from the DML compiler. It is similar to selecting
the best nerve to carry the waves to brain! · Stored Data Manager: - This is also known as Database Control
System. It is one the main central system of the database. It is responsible
for various tasks oIt converts the requests received from query
optimizer to machine understandable form. It makes actual request
inside the database. It is like fetching the exact part of the brain to
answer. oIt helps to maintain consistency and
integrity by applying the constraints. That means, it does not allow
inserting / updating / deleting any data if it has child entry. Similarly it
does not allow entering any duplicate value into database tables. oIt controls concurrent access. If there is
multiple users accessing the database at the same time, it makes sure, all of
them see correct data. It guarantees that there is no data loss or data
mismatch happens between the transactions of multiple users. oIt helps to backup the database and recover
data whenever required. Since it is a huge database and when there is any
unexpected exploit of transaction, and reverting the changes are not easy. It
maintains the backup of all data, so that it can be recovered. · Data Files: - It has the real data stored in it. It can be stored as magnetic
tapes, magnetic disks or optical disks. · Compiled DML: - Some of the processed DML statements (insert, update, delete)
are stored in it so that if there is similar requests, it will be re-used. · Data Dictionary: - It contains all the information about
the database. As the name suggests, it is the dictionary of all the data
items. It contains description of all the tables, view, materialized views,
constraints, indexes, triggers etc. References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill Elmasri, Navathe, “Fundamentals Of Database Systems”, Pearson
Educations.
|
|
Unit-01/Lecture-05 |
||||||||
|
Functions
of DBA and Designer: People who deal with databases
Many
persons are involved in the design, use and maintenance of any database.
These persons can be classified into 2 types as below. 1.Database Administrators (DBA): ·
The DBA is responsible for authorizing access to the
database, for Coordinating and
monitoring its use and for acquiring software and hardware resources as
needed. ·
These are the people, who maintain and design the database
daily. ·
DBA is responsible for the following issues. ·
a. Design of the
conceptual and physical schemas: The DBA is responsible for
interacting with the users of the system to understand what data is to be
stored in the DBMS and how it is likely to be used. The DBA creates the original
schema by writing a set of definitions
and is Permanently stored in the 'Data Dictionary'. ·
b. Security and
Authorization: The DBA is responsible for
ensuring the unauthorized data access is not permitted. The granting of different types of
authorization allows the DBA to regulate which parts of the database various
users can access. ·
c. Storage
structure and Access method definition: The DBA creates
appropriate storage structures and access methods by writing a set of definitions,
which are translated by the DDL compiler. ·
d. Data
Availability and Recovery from Failures: The DBA must take steps to ensure
that if the system fails, users can continue to access as much of the
uncorrupted data as possible. The DBA also work to
restore the data to consistent state. ·
e. Database
Tuning: The DBA is responsible
for modifying the database to ensure adequate Performance as requirements
change. ·
f. Integrity
Constraint Specification: The integrity constraints are kept
in a special system structure that is consulted by the DBA whenever an update
takes place in the system. 2.Database
Designers: ·
Database designers are responsible for identifying the
data to be stored in the database and for choosing appropriate structures to
represent and store this data. 3. End Users: ·
People who wish to store and use data in a database. ·
End users are the people whose jobs require access to the
database for querying, updating and generating reports, listed as below. ·
a. Casual End users: These
people occasionally access the database, but they may need different information each time. ·
b. Naive or Parametric End Users: Their
job function revolves around constantly querying and updating the database
using standard types of queries and updates. ·
c. Sophisticated End Users: These
include Engineers, Scientists, Business analyst and others familiarize to
implement their applications to meet their complex requirements. ·
d. Stand alone End users: These
people maintain personal databases by using ready-made program packages that
provide easy to use menu based interfaces.
·
These people determine the requirements of end users and
develop specifications for transactions. 5.Application Programmers (Software Engineers): ·
These people can test, debug, document and maintain the
specified transactions. OVERVIEW OF DATABSE
DESIGN ·
The problem of database design is stated as below. 'Design
the logical and physical structure of 1 or more databases to accommodate the
information needs of the users in an organization for a defined set of
applications'. ·
The goals of database designs are as below. 1.
Satisfy the information content requirements of the specified users and
applications. 2.
Provide a natural and easy to understand structuring of the information. 3. Support
processing requirements and any performance objectives Such as 'response
time, processing time, storage space etc. Following
steps are used for designing a database: 1.
Requirements
Collection and Analysis: *
This is the first step in designing any database
application. *
This is an informal process that involves discussions and
studies and analyzing the expectations of the users & the intended uses
of the database. *
Under this, we have to understand the following. 1. What data is to be stored n a database? 2. What applications must be built? 3. What operations can be used? *
Example: For customer database, data is
cust-name, cust-city, and cust-no. 2.
Conceptual
database design: *
The information gathered in the requirements analysis step
is used to develop a higher-level description of the data. *
The goal of conceptual database design is a complete
understanding of the database structure, meaning (semantics), inter-relationships
and constraints. *
Characteristics of this phase are as below. 1.Expressiveness: The data model should be
expressive to distinguish different types of data, relationships and
constraints. 2.Simplicity and Understand
ability: The model should be
simple to understand the concepts. 3.Minimality: The model should have
small number of basic concepts. 4.Diagrammatic Representation: The
model should have a diagrammatic notation for displaying the conceptual
schema. 5.Formality: A conceptual schema
expressed in the data model must represent a formal specification of the data. *
Example: Cust_name
: string; Cust_no : integer; Cust_city : string; 3.
Logical
Database Design: *
Under this, we must choose a DBMS to implement our database
design and convert the conceptual database design into a database schema. *
The choice of DBMS is governed by number of factors as
below. 1. Economic Factors. 2. Organizational
Factors. Explanation
is as below. a. Economic Factors: These
factors consist of the financial status of the applications. Software
Acquisition Cost: This consists buying
the software including language options such as forms, menu, recovery/backup
options; web based graphic user interface (GUI) tools and documentation. Maintenance
Cost: This is the cost of receiving
standard maintenance service from the vendor and for keeping the DBMS version
up to date. Hardware
Acquisition Cost: This is the cost of additional
memory, disk drives, controllers and a specialized DBMS storage. Database
Creation and Conversion Cost: This is the cost of creating the
database system from scratch and converting an existing system to the new
DBMS software. Personal
Cost: This is the cost of
re-organization of the data processing department. Training
Cost: ` This is the cost of training for
Programming, Application Development and Database Administration. Operating
Cost: The cost of continued operation of
the database system. b.
Organizational Factors: These
factors support the organization of the vendor, can be listed as below. Data
Complexity: Need of a DBMS. Sharing
among applications: The greater the sharing among
applications, the more the redundancy among files and hence the greater the
need for a DBMS. Dynamically
evolving or growing data: If the data changes constantly, it
is easier to cope with these changes using
a DBMS than using a file system. Frequency
of ad hoc requests for data: File systems are not suitable for
ad hoc retrieval of data. Data
Volume and Need for Control: These 2 factors needs for a DBMS. ·
Example: Customer database can be
represented in the form of tables or
diagrams. c.
Schema Refinement: ·
Under this, we have to analyze the collection of relations
in our relational database schema to identify the potential problems. 4.Physical
Database Design: ·
Physical database design is the process of choosing
specific storage structures and access paths for the database files to
achieve good performance for the various database applications. ·
This step involves building indexes on some tables and
clustering some tables. ·
The physical database design can have the following
options. 1.Response Time: This is the elapsed
time between submitting a database transaction for execution and receiving a
response. 2.Space Utilization: This is the amount of
storage space used by the database files and their access path structures on
disk including indexes and other access paths. 3.Transaction Throughput: This is the average
number of transactions that can be processed per minute. 4.
Security
Design: ·
In this step, we must identify different user groups and
different roles played by various users. ·
For each role, and user group, we must identify the parts
of the database that they must be able to access, which are as below. References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri,
Navathe, “Fundamentals Of Database Systems”, Pearson Educations.
|
UNIT 1/LECTURE 6 |
||||||||||||||||
|
Entity Relational Model (E-R Model) The E-R model can be
used to describe the data involved in a real world enterprise in terms of
objects and their relationships. Uses: ·
These models can be used in database design. ·
It provides useful concepts that allow us to move from an
informal description to precise
description. ·
This model was developed to facilitate database design by
allowing the specification of overall logical structure of a database. ·
It is extremely useful in mapping the meanings and
interactions of real world enterprises onto a conceptual schema. ·
These models can be used for the conceptual design of
database
applications. ENTITIES ·
It
is a collection of objects. ·
An entity is an object that is distinguishable from other
objects by a set of attributes. ·
This is the basic object of E-R Model, which is a 'thing'
in the real world with an independent existence. ·
An entity may be an 'object' with a physical existence. ·
Entities can be represented by 'Rectangles'. ·
Example: Customer, account etc. ATTRIBUTES ·
Characteristics of an entity are called as an attribute. ·
The properties of a particular entity are called as
attributes of that specified entity. ·
Example: Name, street_address, city --- customer database. Acc-no, balance --- account database. ·
Types: These can be classified into
following types. 1.
Simple Attributes. 2.
Composite Attributes. 3.
Single Valued Attributes. 4.
Mutivalued Attributes. 5.
Stored Attributes. 6.
Derived Attributes. Explanation
is as below. 1.Simple
Attributes: ·
The attributes that are not divisible are called as
'simple or atomic attributes'. ·
Example: cust_name, acc_no
etc.. 2.Composite
Attributes: ·
The attributes that can be divided into smaller subparts,
which represent more basic attributes with independent meaning. ·
These are useful to model situations in which a user
sometimes refers to the composite attribute as unit but at other times refers
specifically to its components. ·
Example: Street_address can be
divided into 3 simple attributes as Number, Street and Apartment_no. Street_address City State Zip 3.Single
Valued Attribute: ·
The attributes having a single value for a particular entity
are called as 'Single Valued Attributes'. ·
Example: 'Age' is a single
valued attribute of 'Person'. 4.Muti
Valued Attribute: ·
The attributes, which are having a set of values for the
same entity, are called as 'Multi Valued Attributes'. ·
Example: A 'College Degree'
attribute for a person.i.e, one person may not have a college degree, another
person may have one and a third person may have 2 or more degrees.
5.Derived
Attributes:
‘Age’
attribute is derived from another attribute ‘Date’. 6.Stored
Attribute:
In
the above example,’ Date’ is a stored attribute References: Elmasri, Navathe, “Fundamentals Of Database Systems”, Pearson
Educations. ENTITY
SETS Entity
Type: ·
A collection entities that have the same attributes is
called as an 'entity type'. ·
Each entity type is described by its name and attributes. Entity
Set: ·
Collection of all entities of a particular entity type in
the database at any point of time is called as an entity set. ·
The entity set is usually referred to using the same name
as the entity type. ·
An entity type is represented in ER diagrams as a
rectangular box enclosing the entity type name. ·
Example: Group of employees. Strong Entity Set: Entity types that have
key attributes of their own are called as strong entity types.
Weak Entity Set: Entity types that do not
have key attributes of their own are called as weak entity types.
A weak entity type always has a ‘total participation constraint’.
a.
The owner entity set and the weak entity set must
participate in a One-to-many relationship set, which is
called as the ‘Identifying Relationship Set’ of the weak entity
set. b.
The weak entity set must have total participation in the
identifying relationship set. Example: ‘Dependents’
is an example of a weak entity set. Partial
key of the weak entity set: ·
The set of attributes of a weak entity set that uniquely
identify a weak entity for a given owner entity is called as ‘partial key of
the weak entity set’. ·
Example: ‘Pname’ is a partial key for dependents. RELATIONSHIPS ·
It is defined as an association among entities. RELATIONSHIP
SETS ·
It is a collection of relationships. Representation: 1.
Rectangles ---- ___
Entities. 2.
Ellipses -----
------ Attributes. 4.
Diamonds -----
Relationships. Key Attribute. 6.
Doubled Lined Ellipse ---- Multi
Valued Attribute. 7.
Dashed Ellipse ----
Derived Attributes. 8.
Double Lined Rectangle ---- ---- Weak
Entity Set. 9.
Double Lined Diamond ---- ---- Weak Entity Relationship. Identifying
Relationship. Set. 12.Curved
Inside Rectangle ---- --- End Users References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
|
UNIT 1/LECTURE 7 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Defining
the ER Diagram: Street City Customer Cust_acc Account
Dname Budget Street City Employees Instance:
2222 3333 2/2/94 3/3/96 Ternary Relationship:
ADDITIONAL FEATURES OF E-R MODEL
An employee is allowed to work in different departments
and a department is allowed to have several employees. Dname Budget Street City Employee
1 employee can be associated with many departments, where
as each department can be associated with at most 1 employee as its manager. Dname Budget Street City Employee 3.Many to One: Each employee works in at
most 1 department.i.e, many employees can work in same department.
Dname Budget Street City Employee 4.One to One: Each employee can manage at most 1
department. Dname Budget Street City Employee 2.Participation Constraints: ·
The participation constraint specifies whether the
existence of an entity depends on its being related to another entity via the
relationship type. ·
A department has at most one manager. This requirement is
an example of participation constraints. ·
There are 2 types of participation constraints, which are
as below. 1.
Total. 2.
Partial. Explanation
is as below. ·
1.Total: An
entity set dependent on a relationship set and having Oneto
many relationships is said to be ‘total’. The
participation of the entity set ‘departments’ in the relationship Set
‘manages’ is said to be total.
A participation that is not total is
said to be partial.
Participation of the entity set ‘employees’ in ‘manages’
is partial, since not every employee gets to manage a department. ·
In E-R diagram, the total participation is displayed as a
‘double line’ connecting the participating entity type to the relationship,
where as partial participation is represented by a single line. ·
If the participation of an entity set in a relationship
set is total, then a thick line connects the two. ·
The presence of an arrow indicates a key constraint. Employees Name Cost Pname ---------- 4.Aggregation:
Aggregation versus Ternary Relationship:
Entity
set or second relationship set. b.
The choice may also guided by certain integrity constraints that we want to express Employees No Name Monitors Budget Sponsors Since Budget ---------------------------------------------------------------------------------------------------------------------- ·
According to the above diagram, 1. A project can be sponsored by any number of
departments. 2. A department can sponsor 1 or more projects. 3.1 Or more
employees monitor each sponsorship. (Many to
Many Relationship) ·
Consider the constraint that each relationship be
monitored by at most 1 employee. ·
We cannot express this constraint in terms of the ternary
relationship in the following diagram. In that we are using a ternary
relationship instead of aggregation. ·
Aggregation groups a part of an E-Are diagram into a
single entity set allowing us to treat the aggregate entity set as a single
unit without concern for the details of it’s internal structure. ·
Thus, the presence of such a constraint serves as another
reason for using aggregation rather than a ternary relationship set. Generalization is a bottom-up approach in which two lower level entities
combine to form a higher level entity. In generalization, the higher level
entity can also combine with other lower level entity to make further higher
level entity. Specialization
Specialization is opposite to Generalization. It is a top-down approach in
which one higher level entity can be broken down into two lower level entity.
In specialization, some higher level entities may not have lower-level entity
sets at all. Aggregration
Aggregration
is a process when relation between two entity is treated as a single entity.
Here the relation between Center and Course, is acting as an Entity in
relation with Visitor. References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
|
UNIT 1/LECTURE 8 |
|||||||||||||||||||||||||||||||||||
|
·
The information gathered in the requirements analysis step
is used to develop a higher-level description of the data. ·
The goal of conceptual database design is a complete
understanding of the database structure, meaning (semantics),
inter-relationships and constraints. ·
Characteristics of this phase are as below. 1.Expressiveness: The data model should be
expressive to distinguish different typesof data, relationships and
constraints. 2.Simplicity and Understandability: The model should be
simple to understand the concepts. 3.Minimality: The model should have
small number of basic concepts. 4.Diagrammatic Representation: The model should have
a diagrammatic notation for displaying the conceptual schema. 5.Formality: A conceptual schema
expressed in the data model must represent a formal
specification of the data. ·
Example: Cust_name:
string; Cust_no:
integer; Cust_city:
string; a.
Entity Versus Relationships:
Dname Budget Street City Customer
So
this leads to redundancy.
Dbudget Mgr_appt Dname Budget Street City Customer Conceptual Database Design For Large Enterprises ·
The process of conceptual database design consists
describing small fragments of the application in terms of E-R diagrams. ·
For a large Enterprise, the design may require, 1. More than 1 designer. 2. Span data and application
by a number of user groups. ·
Using a high level semantic data model such as ER diagrams
for conceptual design offers the additional advantages that, 1.
The high level design can be diagrammatically represented. 2.
Many people, who provide the input to the design process, easily understand
it. ·
An alternative approach is to develop separate conceptual
schemas for different user groups and then integrate all those. ·
To integrate, we must establish correspondences between
entities, relationships and attributes, so that this process is somewhat
difficult. ·
The relations of degree 1 are called as ‘Unary Relations’. ·
The relations of degree 2 are called as ‘Binary
Relations’. ·
The relations of degree 3 are called as ‘Ternary
Relations’. ·
The relations of degree n are called as ‘nary Relations’. References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
|
UNIT 1/LECTURE 9 |
||||||||||||
|
·
In a hierarchical
model, data is organized into a tree-like
structure, implying a single parent for each record. A sort field keeps
sibling records in a particular order. ·
Hierarchical structures were widely used in the early mainframe
database management systems, such as the Information Management System (IMS) by IBM, and now
describe the structure of XML documents.
·
This structure allows one one-to-many relationship between two
types of data. ·
This structure is very efficient to describe many relationships
in the real world; recipes, table of contents, ordering of paragraphs/verses,
any nested and sorted information. ·
This hierarchy is used as the physical order of records in
storage. Record access is done by navigating downward through the data
structure using pointers combined with sequential accessing. ·
Because of this, the hierarchical structure is inefficient for
certain database operations when a full path is not also included for each
record. Network
Model: ·
The network model expands upon the hierarchical structure, allowing many-to-many
relationships in a tree-like structure that allows multiple parents. ·
The network model organizes data using two fundamental concepts,
called records and sets. Records contain fields (which may be organized
hierarchically, as in the programming language COBOL). Sets
(not to be confused with mathematical sets) define one-to-many relationships between records: one owner, many members. A record
may be an owner in any number of sets, and a member in any number of sets. ·
A set consists of circular linked
lists where one
record type, the set owner or parent, appears once in each circle, and a
second record type, the subordinate or child, may appear multiple times in
each circle. ·
In this way a hierarchy may be established between any two
record types, e.g., type A is the owner of B. At the same time another set
may be defined where B is the owner of A. Thus all the sets comprise a
general directed graph (ownership defines a direction), or network construct.
·
Access to records is either sequential (usually in each record
type) or by navigation in the circular linked lists. ·
The network model is able to represent redundancy in data more
efficiently than in the hierarchical model, and there can be more than one
path from an ancestor node to a descendant. ·
Network databases generally implement the set relationships by
means of pointers that directly address the location of a record on disk. This
gives excellent retrieval performance, at the expense of operations such as
database loading and reorganization. Relational Model: ·
The relational model was introduced by E.F.
Codd in 1970[1] as a way to make database management systems more independent of
any particular application. ·
It is a mathematical model defined in terms of predicate logic and set theory, and
systems implementing it have been used by mainframe, midrange and
microcomputer systems. ·
The basic data structure
of the relational model is the table, where information about a particular
entity (say, an employee) is represented in rows (also called tuples) and
columns. Thus, the "relation" in
"relational database" refers to the various tables in the database;
a relation is a set of tuples. The columns enumerate the various attributes
of the entity (the employee's name, address or phone number, for example),
and a row is an actual instance of the entity (a specific employee) that is
represented by the relation. As a result, each tuple of the employee table
represents various attributes of a single employee. ·
All relations in a relational database have to adhere to some
basic rules to qualify as relations. First, the ordering of columns is
immaterial in a table. Second, there can't be identical tuples or rows in a
table. And third, each tuple will contain a single value for each of its
attributes. ·
One of the strengths of the relational model is that, in
principle, any value occurring in two different records (belonging to the
same table or to different tables), implies a relationship among those two
records. ·
A key that can be used to uniquely identify a row in a table is
called a primary key. ·
A key that has an external, real-world meaning (such as a
person's name, a book's ISBN, or a
car's serial number) is sometimes called a "natural" key. In
practice, most databases have both generated and natural keys, because
generated keys can be used internally to create links between rows that
cannot break, while natural keys can be used, less reliably, for searches and
for integration with other databases. ·
The most common query language used with the relational model is
the Structured Query Language (SQL). References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|