Ez Computerहिन्दीOpen app

Databases, SQL and MS Access

Preview Part I: Security, Data and Digital India · Chapter 32 · 22 min read · 62 practice questions

After this chapter you will be able to

  • distinguish data, database, DBMS and RDBMS
  • compare a DBMS with a traditional file system
  • explain relational terms: table, tuple, attribute, domain, degree, cardinality
  • identify primary, candidate, alternate, super, foreign and composite keys
  • name data models, normal forms and SQL command groups
  • use MS Access objects and data types
  • explain data warehouse, data mining and big data
Databases Basics DBMS = software RDBMS: related tables Codd, 1970: relational Data: bit to database Relational terms Degree = columns Cardinality = rows Null = missing value Relational algebra: σ, π, ⋈ Keys Primary: unique, not NULL Foreign key links tables Candidate: minimal key Composite: 2+ columns Models and E-R Hierarchical = tree Network = graph Relational = tables E-R: rectangle, diamond Normalization and SQL 1NF: atomic values 2NF: no partial dependency 3NF: no transitive dependency SQL: DDL, DML, DCL, TCL Access and ACID MS Access: .accdb Table, query, form, report AutoNumber: unique number ACID: all or nothing
Chapter 32 at a glance Recall the database terms, the relational terms, the keys, the data models, the normal forms, the SQL command families and the Access objects.

32.1 Basic Concepts

External level View level: what each user sees Conceptual level Logical: whole structure, tables, links Internal level Physical: files, pages, indexes on disk Logical data independence Physical data independence
Three levels of a DBMS (ANSI/SPARC) Each level hides the level below it. Changing the disk layout does not change the logical design (physical independence). Changing tables need not change what users see (logical independence).
Term Meaning
Data Raw facts
Database An organised collection of related data stored for easy access and management
DBMS (Database Management System) Software to create, store, retrieve, update and manage databases — e.g., MS Access, MySQL, Oracle
RDBMS (Relational DBMS) DBMS that stores data in related tables — e.g., MySQL, Oracle, SQL Server, PostgreSQL, MS Access, IBM DB2
Database system Database + DBMS + users + applications
DBA (Database Administrator) Person who manages the database, security and backups
Metadata / data dictionary “Data about data” — structure, types, constraints
Schema Overall design/structure of the database
Instance Data in the database at a particular moment
Index Extra structure that helps find rows quickly, like a catalogue

★ EXAM POINT — The relational model was proposed by E. F. (Edgar) Codd of IBM in 1970; he is called the father of the relational database. Codd’s 12 rules define a true RDBMS.

File System vs DBMS

Problem in file system How DBMS solves it
Data redundancy (same data in many files) Central storage, controlled redundancy
Data inconsistency (copies disagree) Single updated copy
Difficult data access Query languages (SQL)
Poor security User permissions
No concurrent access control Many users at once, safely
Integrity problems Constraints (keys, rules)
No backup/recovery Built-in backup and recovery

Disadvantages of DBMS: higher cost, complexity, needs trained staff, bigger hardware.

A simple file holds one kind of data. A database holds many related tables and the relationships between them, and the DBMS controls access, security and consistency. A database is therefore the more advanced, organised form of file-based storage.

Three-Level Architecture (ANSI/SPARC)

Level Also called What it shows
External View level What each user sees
Conceptual Logical level Whole database structure (tables, relationships)
Internal Physical level How data is actually stored on disk

32.2 Relational Model Terms

Roll_No Name Class City 101 Asha 10 Patna 102 Ravi 9 Jaipur 103 Meena 10 Jodhpur 104 Tara 11 Kota Degree = 4 columns: WIDE Cardinality = 4 rows: TALL "Degree is wide, cardinality is tall."
Degree is wide, cardinality is tall Degree counts the columns across the table. Cardinality counts the rows down the table.
FigureAnatomy of a Relation
Table
TermRelation
Column
TermAttribute / field
Row
TermTuple / record
No. of columns
TermDegree
No. of rows
TermCardinality
Allowed values
TermDomain
Unique row ID
TermPrimary key
Link to another table
TermForeign key
Relational term Common term Meaning
Relation Table Data arranged in rows and columns
Tuple Row / record One complete entry
Attribute Column / field One property (Name, Age)
Domain — Set of allowed values for an attribute
Degree — Number of attributes (columns)
Cardinality — Number of tuples (rows)
Null — Missing/unknown value (not zero, not blank space)

🧠 REMEMBER — “Degree is wide, cardinality is tall.” Degree counts the columns across the table; cardinality counts the rows down the table.

Relational Algebra

Relational algebra is the set of operations performed on relations. SQL queries are based on it.

Operation Symbol What it does
Select σ Picks the rows that satisfy a condition
Project π Picks chosen columns (duplicate rows removed)
Union ∪ Rows present in either relation
Intersection ∩ Rows present in both relations
Set difference − Rows in the first relation but not in the second
Cartesian product × Pairs every row of one relation with every row of the other
Join ⋈ Combines rows of two relations that match on a common attribute

What the full chapter covers

The full chapter has these sections:

  1. 32.1 Basic Concepts
  2. 32.2 Relational Model Terms
  3. 32.3 Keys
  4. 32.4 Data Models
  5. 32.5 Normalization
  6. 32.6 SQL — Structured Query Language
  7. 32.7 Microsoft Access
  8. 32.8 Popular Database Software
  9. 32.9 Big Data, Data Warehouse and Data Mining
  10. 32.10 DBMS History and Tools
  11. 32.11 DBMS Components
  12. 32.12 Types of Database
  13. 32.13 Data Hierarchy
  14. 32.14 Open-Source and Closed-Source DBMS
  15. 32.15 Other DBMS Terms
  16. 32.16 Database Architecture (Tiers)
  17. 32.17 More Exam Facts
  18. Chapter Quick Revision
  19. Most Important Terms
  20. Common Confusions
  21. Rapid Recall

Sample questions from this chapter

1. Moving data from one system or format to another?

  1. Data migration
  2. Data mining
  3. Data warehouse
  4. Denormalization
Show answer

Answer: A. Data migration

Data migration moves data from one system, storage location or format to another.

2. Extra structure that speeds up data search?

  1. Schema
  2. Page
  3. Index
  4. Record
Show answer

Answer: C. Index

An index is an extra structure that helps find rows quickly, like a catalogue.

3. Extension of Access 2007+ database?

  1. .accdb
  2. .mdb
  3. .pptx
  4. .docx
Show answer

Answer: A. .accdb

The .accdb extension is used by MS Access 2007 and later database files.

4. Access Number field size for Double?

  1. 8 bytes
  2. 2 bytes
  3. 4 bytes
  4. 16 bytes
Show answer

Answer: A. 8 bytes

A Double Number field uses 8 bytes; Single uses 4 bytes and Integer uses 2 bytes.

5. Access shortcut that opens the Zoom box?

  1. Shift + F2
  2. Ctrl + F
  3. F7
  4. Ctrl + ;
Show answer

Answer: A. Shift + F2

Shift + F2 opens the Zoom box to view and edit the active field's content.