Databases MCQ Flashcards
What is Data?
A collection of facts and figures that can be processed to produce info
What is a database?
a collection of related data designed to meet the information needs of an organisation
DBMS
a database management system is the software that manages and controls access to the database.
Database application
a program that interacts with the database at some point in its execution
Database system
a collection of application programs that interacts with the database and the DBMS
Limits of file Based approach
Separation and Duplication of data
Data Dependence
Incompatible file Formats
Fixed queries
Entity
a distinct object in the org that is to be represented in the database (person,place,thing)
attribute
property that describes some aspect of the object we wish to record
relationship
an association between entities
Where is computer data stored?
In main memory (RAM)
In secondary memory (USB,Flash drives etc)
Why not always use RAM?
RAM is volatile, as it needs power to store and process data meaning when power is witched off, data is lost.
Reasons to store in disk/hard memory
Capacity
Volatility
Implications of 2 types of memory
- What data is stored
- How it is stored
- Performance of a DBMS
Meta Data
Data that describes other data
Three types of data
Processing Data,
Programs,
Meta data
Why are there many application programming languages
Application type,
language design,
data typing,
data independece
Strongly typed
In strongly typed languages, type constraints are strictly enforced by the compiler or runtime system (Java, C#, and Best eg: Arduino int,long,char)
Weakly typed
In weakly typed languages, type constraints are more relaxed, allowing for implicit type conversions and flexible operations between different types.(JavaScript, PHP, and Perl.)
Procedural language
program lists step by step the sequence of processing instructions, C, Java,Python
Non-procedural
the program states the logic required to isolate
the data, but not a procedure on how to do it. MYSQL
MYSQL is
Weakly typed and non procedural primarily
Design objectives in DBMS programs
- Implementation of logical and physical data dependence.
- Data sharing
- Strict control over data
Physical DI
(DI = data independece)independence from the physical hardware
Advantage of physical DI
Allows the program to respond to changes to any physical element within the system
Logical DI
The ability to change the logical structure of the database without affecting the application programs that access the data.
SELECT Name,address
FROM Students;
Is this a logically independent query?
Yes
Why is MYSQL non-procedural?
The SQL programmer just indicates what data is required
rather than how to get it
What’s the problem with data sharing?
if data is shared it must have a common definition to all users/applications
Two sections of MYSQL
Data Definition
Data Manipulation
Data definition (DDL)
records structures and attribute data types.(INSERT,UPDATE,CREATE)
Data Manipulation (DM)
WHERE/FROM/SELECT etc.
Order of a full sample query
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Impact of Restrict data structure to a table
Makes it common to all users
No need for output formatting statements
User operations simplified
4 Basic Properties of Relational Model
- Data only stored in table
- System can use multiple tables
- Data in diff tables can be linked using relationships.
- Each row in a table is unique and you must declare a primary key
Terminology
Relation = Table = File
Tuple = Row = Record
Attribute = Column = Field
Flat files
Flat file = data files that contain records with no structured relationships
Data Model
Data Model = integrated collection of concepts for describing and manipulation data,relationships between data and constraints on the data in an org.
Purpose is to represent data and make data understandable
Components of data model
Structural part
Manipulative Part
Integrity Constraints
Fortnite
Ninja
Relational Data Model
divides info into logical subsets and places each subset in a separate table
Relation/Table
Logical concept, not a representation of the physical structure.
Domain
definition of the allowable set or range of values allowed in an attribute
Primary Key
primary key constraints will uniquely identify each record in a table.
they must contain unique values and can’t contain null values.
there can be only one primary key in a table eg StudentNo
Candidate key
when more than one combination of attributes uniquely identify a row.
Alternate key
Candidate keys are unique but also minimal in that
they cannot be reduced. That is, no subset of the key can
be a candidate key itself (extra attribute(s) are
redundant).
Why is key uniqueness important
Search Mechanism
Prevents duplication,waste of storage
Count function
Implement relationships (JOINS)
Referential Integrity
There can be no unmatched foreign key values
Required Fields
Marked as NOT NULL
Why use DBMS
Data Management & sharing
Data Integrity
Security
Recovery
Concurrency
Alternatives to RDBMS
NoSQL
MongoDB
HADOOP
DBA
Database Administrator manages and controls the DBMS tools
Brief History
Big changes - 1992
Made - 1972
Maker - Donald D. Chamberlin
SQL 3 - 1999
Latest - SQL:2016 released
SQL Dialects
MYSQL,SQLLite, Oracle,Ingres,MYSQL server, DB2
Objectives of SQL
Portable
Create database and relation structures
Perform inserts,modifications,deletion
Perform Simple/Complex queries
Reserved Words
fixed part of SQL and must be spelt as required and cannot be split across lines
Portability
Ability to move code from one database installation to another.
Easiest eg: using ‘’ instead of “ “. as some dialects don’t support double quotes
User-defined words
made up by user and represent names of database objects.
BNF notation (kind of an aside?)
UPPER CASE -reserved words
lower case - user defined
| indicates choice
{} indicates required element
[] indicates optional element
… indicates optional repitition
SELECT
query data in db
UPDATE
updates data in a table
INSERT
inserts data in table
WHERE
filters rows
DELETE
deletes data from table
USE
specifies database to be used
FROM
specifies table to be used
HAVING
filters groups subject to some conditon. needs GROUP BY to work
ORDER BY
specifies the order of the output
AS
creates temporary column
GROUP BY
forms groups of rows with same column value. isolates subsets
DISTINCT
eliminates duplicates
BETWEEN
creates a range of values between such a number and another number (BETWEEN 2000 AND 300)
Mandatory queries
SELECT FROM
IN
allows you to specify multiple values in a WHERE clause
LIKE
search for a specified pattern in a column
%sampleword%
looks for sequence of characters of any length containing sampleword
_sampleword
looks for a match with only one character before sampleword
sampleword_
looks for a match with only one character after sampleword
IS NULL
checks if a value is NULL
IS NOT NULL
checks for non null values
Difference between WHERE and HAVING
WHERE: filters rows
HAVING: filters groups
Subquery
Query inside a Query (SELECT inside a SELECT)
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo=(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’)
