DataBase Fundamentals

1

• DataBase Fundamentals
• Vivek Gupta
• IT Systems Group
• Indian Institute of Management
• Lucknow

2
What is a Database ?

• A database is a organised collection of
  information with a common purpose
• stored in one or more well-defined tables.
• Databases are organized by fields, records and
  files
• A file is a collection of records.
• For example, a telephone book is analogous to a
  file. It contains a list of records, each of
  which consists of three fields name, address,
  and telephone number
• The rows, referred to as database records, in a
  database table are used to describe similar
  items.In general, no two rows in a database table
  will be alike.
• The columns, called database fields, in a
  database table provide characteristics of the
  records.
• Each field contains one specific piece of
  information.
• In defining a database field, we specify the data
  type, assign a length, and describe other
  attributes.
• Some field types include Binary, Boolean,
  Counter, Double, Single, Long, Integer, etc.

3
DB Basics (contd)

• A field can be required, optional, or calculated.
  
• A required field is one in which you must enter
  data, while
• an optional field is one you may leave blank.
• A calculated field is one whose value is derived
  from some formula involving other fields.
• You do not enter data into a calculated field
  the system automatically determines the correct
  value.
• To access information from a database, we need a
  database management system (DBMS).
• DBMS A collection of programs that enables us to
  enter/store, organize/modify, and extract/select
  information from a database.
• Types of DBMSs,
• small systems that run on personal computers
• huge systems that run on mainframes.
• Examples of database applications
• computerized library systems
• automated teller machines
• flight reservation systems
• computerized parts inventory systems

4
Client, server, and the SQL interface

• Server software
•  A database management system (DBMS)e.g.,MS SQL
  Server, Sybase SQL Server, Oracle, or DB2.
• Client software
• Application software that does the work that
  the user wants to do.
• ODBC driver or OLE DB provider for the database
  that lets the client application communicate with
  the data source.
• The SQL interface
• SQL queries are sent through the ODBC driver or
  OLE DB provider.
• When the DBMS receives a query, it returns the
  results to the client.

5
Relational Databases

• Databases are made up of many tables stored in a
  single file.
• Each table contains a logical grouping of
  information with its own records and fields.
• To allow cross-referencing of the tables, within
  a database, the tables must have some common
  fields.
• The referral of one table to another via a common
  field is called a relation.
• Relations among tables are established using
  keys.
• A primary key is a field that uniquely identifies
  a record so it can be referenced from a related
  table.
• A foreign key is a field that holds
  identification values to relate records stored in
  other tables

6
Primary, Foreign Key

• Primary key
• One or more fields (columns) whose value(s)
  uniquely identify each record in a table.
• It cannot allow Null values and must always have
  a unique index.
• Used to relate a table to foreign keys in other
  tables.
• Foreign key
• One or more table fields (columns) that refer to
  the primary key field or fields in another table.
  
• Indicates how the tables are related 
• the data in the foreign key and primary key
  fields must match, though the field names do not
  have to be the same. (e.g., a Products table
  might contain the foreign key SupplierNumber,
  which relates to the primary key SupplierID in a
  Suppliers table)
• A foreign key can be Null if a foreign key
  consists of more than one field and any of those
  fields is Null, all the fields must be Null.
  Avoid it

7
Null

• A NULL represents a value that we currently do
  not know
• the only way to represent many sources of unknown
  or missing values
• a NULL is neither a zero nor an empty string of
  characters that we can indicate with a pair of
  quotes
• Are a necessary evil
• consume more storage space than values and retard
  indexing and searching
• NULLs propagate i.e., when we add, subtract,
  multiply or divide numbers and one is NULL, the
  answer is NULL
• ANSI-SQL specifies that NULLs are ignored in
  aggregate functions, such as finding the highest,
  lowest or average values from a set of data

8

• The primary key in a row in one table can be
  used to relate that record to each row in another
  table that has the same value in a foreign key.

9
DB concepts terminology

• Some DBMS let you define one or more non-primary
  keys.
• In SQL Server, non-primary keys are called unique
  constraints or unique keys. Like a primary key, a
  non-primary key uniquely identifies each row in
  the table.
• A table can also be defined with one or more
  indexes.
• An index provides an efficient way of accessing
  the data in a table.
• An index is automatically created for a tables
  primary and non-primary keys.

10
Index

• Index is an ordered list of the data contained in
  a field or a group of fields within a table.
• is used to allow faster access to the information
  in the database.
• We can create an index for any field we might
  want to perform a search on.
• Unique index
• An index defined by setting a field's Indexed
  property to Yes (No Duplicates).
• It will not allow duplicate entries in the
  indexed field.

11
Relationship

• An association established between common fields
  (columns) in two tables
• One-to-one
• when one record in one table is linked to only
  one record in another table.
• One-to-many
• when one record in one table links to many
  records in another table, we say there is a
  one-to-many relation.
• Many-to-many
• when many records in one table are linked to many
  records in another table, we say there is a
  many-to-many relation.

12
Summarising Key Fields

• enable a DBMS to match up records between two
  related tables
• DBMS reads the info. from the FK, find the
  matching record in the PK, and use that match to
  relate the two records
• Are always one or more fields
• Primary key fields hold unique values for each
  record in their table
• Foreign key fields hold values that match values
  in the primary key of another table
• Generally, in a one-to-many relationship the
  one side has a primary key field and the many
  side has a foreign key field

13
Referential Integrity

• Rules that we follow to preserve the defined
  relationships between tables when we enter or
  delete records.
• If we enforce referential integrity, DB prevents
  us from
• adding records to a related table when there is
  no associated record in the primary table,
• changing values in the primary table that would
  result in orphan records in a related table, and
• deleting records from the primary table when
  there are matching related records in a related
  table.

14
Normalisation

• The process of breaking down database tables into
  simpler tables, is known as database
  normalization.
• 0NF
• A DB with loosely related data elements
• 1NF
• Here the DB has elements grouped into tables,
  with no repeating information or compound fields,
  and the data elements should be logically related
• 2NF
• Results in creation of a well-defined table,
  wherein all data elements must relate to the
  primary key
• 3NF
• Herein, a table has no derived information or
  internal dependencies (but relate to primary key)

15
Sample Relational DB (biblio.mdb)

• The books (BIBLIO.MDB) database is made up of
  four tables
• Authors (6,246 records)
• Publishers (727 records)
• Titles (8,569 records)
• Title Author (16,056 records)
• The Authors table contains information about the
  authors of the books in the database.
• It has three (3) fields Au_ID, Name, and Year
  Born
• The Publishers table contains information about
  the publishers in the book database.
• It has ten (10) fields PubID, Name, Company
  Name, Address, City, State, Zip, Telephone, Fax,
  and Comments

16
Biblio.mdb (contd)

• The Titles table contains information about each
  book title in the database.
• It has eight (8) fields Title, Year Published,
  ISBN, PubID, Description, Notes, Subject, and
  Comments
• The Title Author table contains information
  relating book titles to authors within the
  database.
• It has just two fields ISBN (International
  Standard Book Number, a number used by bookstores
  and libraries to reference books) and Au_ID
• Biblio is a well-designed database we can learn
  from

17
(No Transcript)
18
Biblio.mdb (contd)

• A well-designed database has well-defined tables,
  which make database management a far simpler
  task.
• Each table has two types of information
• source data and relational data.
• Source data is actual information, such as names,
  phone numbers, and addresses.
• Relational data are references to data in other
  tables via keys, such as PubID, ISBN, and Au_ID.
• A primary key defines a unique record.
• PubID in the Publishers table, ISBN in the Titles
  Table, and Au_ID in the Authors table are primary
  keys.
• They identify a unique entry in their respective
  table

19
Biblio.mdb (contd)

• A foreign key is a piece of relational
  information in one table that links to
  information in another table.
• In the Titles table, PubID is a foreign key.
• Using a PubID from this table in conjunction with
  the PubID primary key in the Publishers table
  will provide us with complete information about a
  particular publisher.
• In the Title Author table, ISBN and Au_ID are
  foreign keys
• The primary purpose of the books database
  (BIBLIO.MDB) is to track information about book
  titles.
• Though each table gives us a piece of information
  about a particular book, but to get all the
  information about a book, we need all four tables

20
Querying the database
21
Querying a database

• Requests for information from a database are made
  in the form of a query, a stylized question.
• For example, the query
• SELECT ALL WHERE NAME "SMITH" AND AGE gt 35
• requests all records in which the NAME field is
  SMITH and the AGE field is greater than 35.
• The set of rules for constructing queries is
  known as a query language.
• Different DBMSs support different query
  languages, although there is a semi-standardized
  query language called SQL (structured query
  language).

22
(No Transcript)
23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26
(No Transcript)
27
(No Transcript)
28
(No Transcript)
29
(No Transcript)
30
(No Transcript)
31
(No Transcript)
32
(No Transcript)
33
(No Transcript)
34
(No Transcript)
35
(No Transcript)
36
(No Transcript)
37
VB and Databases

• The data control is a VB object that connects the
  application to the database via the database
  engine.
• It is the conduit between the application and the
  engine, passing information back and forth
  between the two.
• The database engine is the heart of a VB DBMS,
  the actual software that does the management.
• The database engine native to Visual Basic is
  known as the Jet engine.

38
VB and Databases

• A VB application acts as a front-end to the
  database.
• i.e., the VB application provides the interface
  between the user and the database.
• The interface allows the user to tell the
  database what the user needs and allows the
  database to respond to the request displaying the
  requested information in some manner
• Examples where we might use Visual Basic as a
  DBMS
• Implementing a new application that requires
  management of a database
• Connecting to an existing database
• Interacting with a database via the internet
• A VB application cannot directly interact with a
  database. There are two intermediate components
  between the application and the database
• the data control and the database engine

39
(No Transcript)
40
(No Transcript)
41
(No Transcript)
42
(No Transcript)
43
(No Transcript)
44
(No Transcript)