database and design

Question 1

what is a field

Answer 1

a field is a single piece of information; e.g. the name of a pupil (can be text,number,boolean,date,time)

Question 2

what is a record

Answer 2

a record is all the information related to one item, object or person.

Question 3

what is a table

Answer 3

a table is a collection of records

Question 4

what is a flat file database

Answer 4

a flat file database is a database made up of one table of data

Question 5

what is a relational database

Answer 5

a relational database is one that contains two or more linked tables of data these links are called relationships

Question 6

relational databases prevent what

Answer 6

data duplication

Question 7

what is an entity

Answer 7

an entity is a person, place, thing, event, concept that data is to be stored on in a school database entities might include CLASS, PUPIL, TEACHER (table names)

Question 8

what is an entity occurrence

Answer 8

entity occurrence is a specific example of an entity. each row in a database contains information describing one entity occurrence

Question 9

what is an attribute

Answer 9

an entity is described by attributes. attributes become Collums (fields)
An attribute is a single piece of information about that entity

Question 10

what is an entity in terms of attributes

Answer 10

an entity is described by attributes. attributes become Collums (fields) imagine a pupil entity, it would have attribute of DoB and Grades (field names)

Question 11

what is a primary key

Answer 11

a primary key is a field that uniquely identitfies one record (or entity occurance)

Question 12

what is a foreign key

Answer 12

a foreign key is a field that is linked to another tables primary key. e.g. a primary key used in another table to link the two tables. any value in the foreign key should match that of the primary key in the other table

Question 13

what are end user requirements

Answer 13

the end users are the people who are going to be using the database

their requirements are the tasks they expect to be able to do whilst using the database for example: preforming searches
sorting a database or using the database to preform calculations

Question 14

what are functional requirements

Answer 14

functional requirements are the processes and activities that the database has to perform in order to meet the end user requirements

it will also need to reference the information that the system has to contain to carry out its functions

Question 15

what is cardinality

Answer 15

cardinality is the relationship between tables including one to one relationships and one to many relationships

Question 16

what is a compound key

Answer 16

a compound key is a primary key that comprises of two or more attributes with each of these attributes being primary keys themselves and is used to link many tables

Question 17

what is an entity occurrence diagram

Answer 17

an entity occurence diagram is a method of identifying the relationship between entities . it is a tall oval with the entities listed inside and lines are drawn between the entities to show cardinality and relationship

Question 18

what is an entity relationship diagram

Answer 18

is used to show the enitites and attributes of the database and the relationship between them

Question 19

what are the 4 types of validation

Answer 19

presence check - checks for presence
restricted choice - usually uses a drop down menu
length check - checks that it is a certain length
range check - checks values are within a certain range

also know look up from

Question 20

data dictionary

Answer 20

a data dictionary is a design notation for data modelling during the design of the database

a data dictionary includes names and description of the tables and fields and any relationships

Question 21

what does SELECT command do

Answer 21

The SELECT command is used to choose with feilds to display

Question 22

What does the FROM command do

Answer 22

the FROM command states where any information used within the query is from

Question 23

what does the WHERE command do

Answer 23

the WHERE command states conditions that must be met for attributes to be displayed

Question 24

what is an equi join

Answer 24

an Equi join is used when you need to display fields from related records from linked tables. it is used in the where statement and consists of saying that one tables compound key equals the other

Question 25

What is the LIKE statement

Answer 25

the LIKE statement is used to preform search operation in the WHERE clause with the % symbol representing any number of characters and _ representing one character

finding a name with the second last letter being a w would look like this WHERE name LIKE ‘%w_’

Question 26

what does the AS statement do

Answer 26

the AS statement is used to create aliases with the syntax looking like SELECT name AS [pupilName]

Question 27

what are the aggregate functions

Answer 27

AVG COUNT MIN MAX SUM

Question 28

what does AVG do

Answer 28

finds and displays the average of a certain field has to be numerical
: AVG(money)

Question 29

what does COUNT do

Answer 29

COUNT displays how many occurrences there are of a certain field works with any data type :
COUNT (pupils)

Question 30

what does SUM do

Answer 30

SUM reterns the value of every value in a field added together only works with numerical data types : SUM(prices)

Question 31

what does MIN/MAX do

Answer 31

MIN returns what the minimum value of a field and MAX returns the Maximum value : MIN(price)

Question 32

what does ROUND do

Answer 32

ROUND rounds a value to a certain number of digits : ROUND(price,2) this would round to two sig figs

ROUND(AVG(price), 2) also works

Question 33

using results of queries in other queries

Answer 33

you need to put the name of the query in square brackets in the FROM clause and the name of the field in square brackets where it is wanted to be used

Question 34

why use the result of queries in other queries

Answer 34

when aggregate functions want to be used outside the SELECT clause

Question 35

what does GROUP BY do

Answer 35

it is used to form groups of records . is needed when using more than one aggregate functions is used as its own clause

Question 36

what does UPDATE clause do

Answer 36

UPDATE allows you to edit what the values within an existing record are syntax looks like this :
UPDATE customer
SET address = ‘123 Dundee road’ ,postcode = ‘dd6 7hj’
WHERE name LIKE ‘W%’

Question 37

what does DELETE clause do

Answer 37

DELETE permanently deletes things:
DELETE *
FROM Accounts
WHERE name = ‘will boyle’

Question 38

how to select all of something

Answer 38

*

Question 39

how to use addition subtraction multiplication and division

Answer 39

SELECT (price * quantity )
FROM customer
WHERE (x+y) = 5

Question 40

what does INSERT do

Answer 40

insert allows you to add new records into database :
INSERT INTO customer
(CustomerNo,FirstName,Surname,AdressTown) VALUES (‘583’,’Ron’,’Swanson’,’123 adress street’,’dd6 blah blah blah’)