Theory Flashcards
(26 cards)
In joins, do matching fields have to have the same data definition and the same names?
Matching fields must have the same data definition (text, numeric, etc.) but they can have different names.
What are database views and what can they do?
Can they update the underlying tables?
Database views are virtual tables. They can:
- provide applications with suitable data
- be part of a database security strategy
- give users access to the data they need
- use data from more than one table
They may be able to update the underlying tables if they contain primary key data.
In an Entity-Relationship Diagram, what does a * represent?
A primary key
What is the main purpose of a database?
To store the persistent data for an application.
What’s the purpose of Middleware?
Name a type of Middleware
Middleware joins front ends to databases, simplifying connecting heterogenous front and back ends.
ODBC is a type of Middleware
What is a foreign key?
A copy of the primary key from another table. They’re used to link tables
What is a database and where are they used?
Structured set of data that’s organised so it can be queried.
Used in business applications, embedded software, computer games, media-rich websites
What does DBMS stand for and what are 5 DBMS products?
Database Management System
Access MS SQL Server MySQL DB2 Oracle
Does a foreign key have to be unique?
Must they link to a primary key?
No, foreign keys aren’t usually unique but they must link to a primary key.
What does SQL stand for?
What can it do? (Give the acronym)
Is it a DML or a DDL?
Structure Query Language. Used to: Create Retrieve Update Delete data [CRUD]
It’s both a DML (Data Manipulation Language) and a DDL (Data Definition Language)
What are two limitations of sub-queries and where would a full join be more appropriate?
They won’t filter across tables and won’t bring back fields across tables.
Use a full join if all fields are needed.
What are views used for?
Storing the SQL into a consistent object.
Security.
Protecting application developers.
Providing column aliases to protect applications from database changes.
How do applications use virtual delete?
- Users press the delete button.
- UPDATE SQL runs (sets DeleteFlag to TRUE)
- Page is refreshed from VIEW.
- Updated records appear to have been deleted.
What do database administrators use to delete tables without ACTUALLY deleting records?
How is this done?
Virtual deletes, with views and filtering.
Adding a deleteFlag to table, updating it to 1 (TRUE)
What does ODBC stand for?
Open Database Connectivity
Name six data types
- Char
- Varchar
- Nvarchar
- Varbinary
- Date
- Datetime
Why is backup essential to keep data safe? (4)
In case of:
- physical damage to location
- physical damage to server
- disk failure
- unintentional/intentional hack that removes/changes data.
What is personal data? Name a few
Information relating to an identified or identifiable person, such as Name Identification number Physical Physiological Genetic Mental Economic Cultural Social identity
Give some examples of sensitive data
Race Ethnic origin Political opinions Religious/philosophical beliefs Union membership Genetics Biometrics Health Sex life/orientation Criminal convictions/offences
What are three types of backup?
Full: whole database, structure and tables
Incremental/differential: incremental copies data changed since last backup, differential is cumulative.
Transactional log: files showing all transactions.
True/false:
A primary key is a constraint.
True, a primary key must be unique.
What are two advantages of a join?
There doesn’t have to be all details in one table.
There isn’t duplicate data.
What data type, varchar or nvarchar, makes the most efficient use of storage and why?
Varchar
It doesn’t set aside storage space if it’s not being used.
Which data type is alphanumeric? What does this mean and what can it be used for?
Nvarchar is alphanumeric. It accepts numbers but won’t process them as numbers. It can be used to hold phone numbers and postcodes.
