databases Flashcards
what is a database
collection of data organised in a way that makes easy to store retrieve and manipulate data either electronic or paper based
what is the advantage of electronic database
more efficent easy to store, mangae and retreive files
single table database
single table makes up database eg. spreadsheet
table
collection of rows and columns
what is a row called in databases
records
what is a record, how should you count its quantity
record is collection of related fields about an entity
do not count first row, as its headers of columns
what do you call columns in databases
fields or attributes
what are fields or attributes
piece of info about an entity (columns)
each field is made up of same datatype
entity
anything about which data can be collected
what is a data dictionary
table that shows collection of fields and their data types and metadata
metadata
data about data, info about existing data
what are the different data types
real, character, integer text/ alphanumeric, boolean, date/time
character
single letter, number, value, eg. s, 7
real
decimals eb. 12.6
integer
whole number can be zero and negative
text/ alphanumeric
string of characters can contain combination of letters with numbers and symbols
Num, Num1, Num_1
data/time
represent date or time, must specify seperator used in between eg. / or - or :
eg. 17/06/24
17-6-43
15:00:34
boolean
TRUE or FALSE
why is data type important for inputting data of database
if wrong data type, data might not be accepted, this is done by validation rules
primary key
field that uniquely identifies one record from all stores in a database table
SQL
structured query language- database manipulation language used to manage data in a database, ued to perform operations on database
what are some things SQL can perform
create database, create table, retreive data from databases, execute queries, display records
what is SELECT…FROM used for, and how
used to specify fields you want to view from database table
3 variations:
SELECT <filedname> FROM <tablename>
SELECT <fieldname1>, <fieldname2> FROM <tablename>
SELECT * FROM <tablename></tablename></tablename></fieldname2></fieldname1></tablename></filedname>
how do you select more than one field to output
list fields you want to slect by adding a coma in between eg
how do you select entire table
adding asteriscs
why is SELCT…..FROM….WHERE used? and how.
outputs data that meets specified criteria
eg.
SELECT <fieldname>
FROM <tablename>
WHERE <condition></condition></tablename></fieldname>
eg. SELECT <fieldname>
FROM <tablename>
WHERE <fieldname> = "dubai"</fieldname></tablename></fieldname>
what are the different operations you can use for <CONDITION></CONDITION>
equal to =
not equal to <>
less than <
greater than >
less than or equal to <=
greater than or equal to >=
how do you add more than one <condition></condition>
use boolean operations
either AND or OR
explain AND operation
both criteria must be met
eg. SELECT <fieldname>
FROM <tablename>
WHERE <fieldname> = "dubai" AND <fieldname> > 16</fieldname></fieldname></tablename></fieldname>
explain OR operation
used when either one or both criteria must be met
how and why do you use ORDER BY
used togehter with select to sort field or data retrieved from table in ascending or descending order, by default ORDER BY automatically ascneding order
eg.
SELECT <fieldname>
FROM <tablename>
WHERE <fieldname> = "dubai" AND <fieldname> > 16
ORDER BY <fieldname>DESC</fieldname></fieldname></fieldname></tablename></fieldname>
what are the two types of ORDER BY
ascending
<fieldname>ASC or descending
<fieldname>DESC
</fieldname></fieldname>
what happens when you use ORDER BY in text (string) fields
orders by ascending or descending alphabetical order
how and why do you use SUM
only used in columns with numeric values, find sum of all values that meet specified criteria
SELECT sum<fieldname>
FROM <tablename>
WHERE <fieldname> > 16</fieldname></tablename></fieldname>
how and why is COUNT used
count number of records (rows) that meet certain criteria, can be text as well as numeral
eg.SELECT count <fieldname>
FROM <tablename>
WHERE <fieldname> = "dubai" AND <fieldname> > 16</fieldname></fieldname></tablename></fieldname>
global varible
is accessible anywhere in the program EXCEPT inside the function where a variable of the same name is defined
– scope is the whole program
– value can be changed anywhere in the program
local variable
only accessible inside the function, and can only be passed back to the global scope as a return value.
- local variables - scope is a defined block of code/subroutine/procedure/function
value cannot be changed elsewhere in the program
what are features of a maintanble program
-meaningful identifiers
– the commenting feature provided by the
programming language
– procedures and functions
– relevant and appropriate commenting of
syntax
purposes of files
-Files provide a way to store data permanently, allowing programs to access and manipulate it later
-Files allow for organised storage of data, making it easier to find, access, and update
-They enable sharing of data between different programs and users
-They support data backup, ensuring data is not lost when a program or computer system shuts down
-They allow for large amounts of data to be stored and managed efficiently
-Data can be transported from one system to another
Explain why a programmer would use procedures and parameters when writing a program.
procedure:
-to enable the programmer to write a collection of programming statements under a single identifier
-to allow procedures to be re-used within the program or in other programs
- to enable different programmers to work on different procedures in the same project
-o make programs easier to maintain due to being shorter.
parameters
-to pass values from the main program to a procedure /
-so that they can be used in the procedure
-allow the procedure / function to be re-used with different data.
how should you output sql language
in order that they appear in table, and in order that they are listed in SELECT
what does verifying ensure
ensure datas has been accurately copied from one source to another
Give two reasons for storing data in a file
data is stored permanently
* data can be moved to another computer
* another copy of data can be made and stored//accessed elsewhere // backup copy
