rdbms
DESCRIPTION
TRANSCRIPT
UNIT 3 : DATABASES &
SQL
Developed by : Ms. Nita Arora Kulachi Hansraj Model School
Ashok Vihar
• Relational Data Model– Concept of Domain– Tuple– Relation– Primary Key, Alternate Key, Candidate key
• Relational Algebra– Selection– Projection– Union– Cartesian Product
Advantages of the DBMS Approach• The Database Approach Versus the
Traditional File Processing Approach
– Storing data in a non-redundant database improves consistency of data.
• There is only one copy of each item of data.
• Unproductive Maintenance avoided.
– In file based systems, changes to file structure cause endless problems on maintenance (re-writing code).
– Using a DBMS, program access is through a limited view (using SQL).
• Thus programs can’t see minor changes to the database.
What is a database?
A database is a collection of related data elements– Tables (entities)– Columns (fields or attributes) – Rows (records)
Some of the Advantages of using a DBMS Sharing of data
Control of redundancy and thus Data consistency Improved data integrity Better data accessibility Representing complex relationships among data Enforcing integrity constraints Better security of data (as long as backup is perfect) More control over concurrency Better backup and recovery procedures Providing multiple interfaces Faster development of new applications Economy of scale
Some of the Disadvantages of using a DBMS High cost of Software
Hardware costs high due to software complexity
Requirement for skilled staff Higher programming costs - greater skill
required Slower processing of some applications Increased vulnerability (all your eggs in
one basket) More difficult recovery (if disaster strikes)
DBMS Architecture• Usually taken to imply a three level architecture. • The complexity of a large database is handled by a
DBMS by providing three views of the data. – An internal view of how the data is stored and accessed
– A conceptual view of what data is available in the database.
– Local or external views which are a limited and simplified view of the database as suitable for a single application or group of associated applications.
• Data abstraction – Users deal with conceptual representation of the data.– They have little control over how the data is stored.
Three Level ArchitectureExternal Level View 1 View 4View 2 View 3
etc.
Conceptual Level Conceptual Schema
Internal SchemaInternal Level
Physical Data Independence
Logical Data Independence
• One of the fundamental aspects of the database approach using a DBMS is that, by providing levels of data abstraction, it hides complexity of data storage from users.
DIFFERENT DATA MODELS
• Hierarchical Data Model• Network Data Model• Relational Data Model
– Relational Model propounded by E.F.Codd
RELATIONAL DATA MODELS : Terminology
• Relation• Attribute / Field / Column• Tuple / Record / Row• Concept of Domain• Concept of Degree• Cardinality• View
RELATIONAL DATA MODELS : Terminology
(Contd….)• Primary Key• Candidate key• Alternate key• Foreign key
Relational Algebra
• The relational algebra is a collection of operations on relations.
• Each operation takes one or more relations as operand and produces another relation as its result
Relational Algebra
• Operations defined in relational algebra include :– Select– Project– Cartesian Product– Union– * Set difference– * Set intersection– * Natural join– * Division
Note : * items not in syllabus
Select• Extracts specified tuples (rows)
from a specified relation (table).
Project• Extracts specified
attributes(columns) from a specified relation.
Join• Builds a relation from two specified
relations consisting of all possible concatenated pairs, one from each of the two relations, such that in each pair the two tuples satisfy some condition. (E.g., equal values in a given col.)
A1 B1A2 B1A3 B2
B1 C1B2 C2B3 C3
A1 B1 C1A2 B1 C1A3 B2 C2
(Naturalor Inner)
Join
Outer Join• Outer Joins are similar to PRODUCT
-- but will leave NULLs for any row in the first table with no corresponding rows in the second.
A1 B1A2 B1A3 B2A4 B7
B1 C1B2 C2B3 C3
A1 B1 C1A2 B1 C1A3 B2 C2A4 * *
Outer Join
Join Items Part # Name Price Count1 Big blue widget 3.76 22 Small blue Widget 7.35 43 Tiny red widget 5.25 74 large red widget 157.23 235 double widget rack 10.44 126 Small green Widget 30.45 587 Big yellow widget 7.96 18 Tiny orange widget 81.75 429 Big purple widget 55.99 9
Invoice # Part # Quantity93774 3 1084747 23 188367 75 288647 4 3
776879 22 565689 76 1293774 23 1088367 34 2
Invoice # Cust # Rep #93774 3 184747 4 188367 5 288647 9 1
776879 2 265689 6 2
Cust # COMPANY STREET1 STREET2 CITY STATE ZIPCODE
1Integrated Standards Ltd. 35 Broadway Floor 12 New York NY 02111
2 MegaInt Inc. 34 Bureaucracy Plaza Floors 1-172 Phildelphia PA 03756
3 Cyber Associates3 Control Elevation Place
Cyber Assicates Center Cyberoid NY 08645
4General Consolidated 35 Libra Plaza Nashua NH 09242
5Consolidated MultiCorp 1 Broadway Middletown IN 32467
6Internet Behometh Ltd. 88 Oligopoly Place Sagrado TX 78798
7Consolidated Brands, Inc.
3 Independence Parkway Rivendell CA 93456
8 Little Mighty Micro 34 Last One Drive Orinda CA 94563
9 SportLine Ltd. 38 Champion Place Suite 882 Compton CA 95328