3.1.15 Database Concepts Flashcards
what database satisfies these requirements:
data is gathered, collated and analysed?
relational database
what is a table?
a two-dimensional representation of data (stored in rows and columns)
how many tables does a flat file database have?
only 1 table
how many tables does a relational database have?
several tables linked together
what is a relational database?
a database structured to recognise relations between stored items of information.
why does each table need a new name?
relational database
so that the database management systems can be found in the right table
What is the standard user interface to any relational database?
structured query language (SQL)
why is SQL used?
for the purpose of:
- retrieving information from a relational database
and
- for gathering data for reports
what is the benefit of a relational database over a flat file database?
it’s straightforward to extend
what does RDBMS stand for?
Relational DataBase Management System
what are the 3 basic components of RDBMS?
- a store
- a method of creating and retrieving data
- a method of ensuring that data is logically consistent
what is a table also referred to in a relational database?
‘relation’
what does a relation store and in what structure?
a two-dimensional structure use to store RELATED information
what does a relational database consist of?
2 or more related tables
what is a record? {3}
- a complete single set of information
- comprised of fields
- a set of records creates a file
what is a row within a relation?
one record
what does a column within a database table contain?
all the information of a single type
what types of data are columns formatted to accept?
(such as:)
- integers
- Booleans
- decimals (real)
- strings
what is a field?
a single piece of data that can be retrieved from a database
(like a cell in a spreadsheet)
what piece of information is a field in an SQL and what does it form part of?
it is the smallest piece of information
forms part of an individual record
what is a database query?
a question you put to the database
what is the outcome of a query?
the information that’s returned by the database to answer the question
how are queries created
using SQL which looks like high-level programming language
every table in a relational database should contain how many primary keys?
1 or more columns that are assigned as the primary key
what is the crucial fact for the primary key to work?
the value it holds must be unique for each of the records within the table
how do data relationships work?
by comparing data in key fields between fields that have corresponding names in linked tables
(basically: by comparing the primary key with the foreign key)
the fields contain the _______ ___ for one of the _____, which then supplies…
the fields contain the PRIMARY KEY for one of the TABLES, which then supplies THE UNIQUE IDENTIFIER FOR EACH RECORD AND THE ‘FOREIGN’ KEY IN THE OTHER TABLE
what is the foreign key?
a collection of attributes intended to match the primary key of a different table
how is a link established between 2 tables?
foreign key
by adding the column that holds the primary key in the 1st table to the 2nd table (becomes foreign key in 2nd table)
3.1.15.1 Query methods (SQL)
press 5 to move on
what are all SQL codes written in the form of?
a query statement and this is ‘executed’ against a database
SQL queries perform…
a data operation (e.g selecting, inserting/updating, or creating data objects)
what does every query statement begin with? {4}
- DELETE
- CREATE
- SELECT
- UPDATE
SQL databases can be created on…
a single machine
often large databases…
are shared between users
how are large databases shared?
by installing them on a dedicated server
3.1.15.2 Connecting to databases from applications and web-based apps
press 5 to move on
2 web-based programming languages
SQL and PHP
other web-based languages:
ASP,
ASP.NET,
Perl and JSP
by storing data in web-based applications, what have new technologies led to?
‘distributed applications’
distributed applications…
have many parts that are stored on virtual machines
do the virtual machines have to be on the same systems?
no they can be on the same or different systems
(virtual machines can be on different systems) what does this allow?
allows smartphones to access the same resources as desktop computers
what are the 2 kinds of distributed software?
- web-based
- client-server
what is web-based software?
allows companies to only pay for what they need and to grow into the system without large upfront costs
(usually paid for on a subscription/ usage basis)
what is client-server software?
usually paid for upfront and the initial cost of the system can be high
advantages of client-server networks over web-based applications: {3}
- you are in control of upgrades and don’t have to update to the latest versions if you don’t want to
- you have to manage your systems and pay for the technical support
- applications tend to run faster when they’re local to the user’s computer
check your understanding question:
what is a database? {2 marks}
databases use a series of tables to store data. a table is a 2-dimensional representation of data stored in rows and columns
check your understanding question:
how does a relational database differ from a flat file database?
a flat file database has 1 table, similar to a spreadsheet. a relational database has multiple linked tables
