U1T2 - Databases Flashcards
Databases
1
Q
What is a database?
A
Collection of data shareable between diff apps. Structured so data can be searched. + retrieved.
2
Q
What are the disadvantages of a paper database?
A
Lots of paper, 1 person at time, long time to find, docs get lost.
3
Q
What are the advantages of computer databases?
A
Lots data, small space, quick, less likely to be lost + multiple users at once.
4
Q
What are the disadvantages of computer databases?
A
Comp equip expensive to buy + install, data collection + setup expensive, staff need training + if system fails, can’t get info.
5
Q
What is a logical database model?
A
Determines if all business’s requirements gathered.
6
Q
What does a logical database model do?
A
Identifies entities + relationships with keys and attributes. Produced during design phase. Gather info about business needs, doesn’t design database, used across various database software + implementations, includes ERD, business process diagrams + user feedback documentation.
7
Q
What is physical database modelling?
A
Depends on software being used in organisation. Software specific.
8
Q
What does a physical database model do?
A
Produced during implementation. Describes table content, fields + records + criteria. Derived from logical. Required for database design, software + hardware specific + has indexes + constraints, includes server model diagram, database design documentation + user feedback documentation.
9
Q
Flat-file System
A
Single table used to hold all data for app. 1 row for each record + 1 column for each field. Data redundancy high here.
10
Q
Relational Database
A
Complex structure made of several related tables consisting of records which contain data linked by foreign and primary keys. Data items uniquely identified by primary key. Tables set up acc. to normalisation rules.
11
Q
Describe tables.
A
Contain records (each holds all data about 1 thing), records divided into fields, each holding 1 piece of data about 1 thing. Fields have data types.
12
Q
What are the diff data types?
A
Text, number, date/time, currency, yes/no + autonumber.
13
Q
Describe these data types:
Text
Number
Date/Time
A
Letters, symbols + numbers (Alphanumeric)
Numbers only (Can hold decimal places)
Dates + Times
14
Q
Describe these data types:
Currency
Yes/No
AutoNumber
A
All monetary data (Includes symbols £, $)
Yes/No, True/False (Data can only be 1 or other)
Unique value generated by Access for each diff record.
15
Q
What is a key field/primary key?
A
Field/attribute that uniquely identifies one record in a table.
16
Q
What is an entity?
A
Object about which data is stored.(table)
17
Q
What are attributes?
A
Data item held in entity. aka field.
18
Q
What is a relationship?
A
Link/association between 2 entities. Foreign key links to primary key. E.g. Dentist + Patient,
19
Q
What are the 3 types of relationship?
A
One-to-One (1:1), One-To Many (1:m) + Many-To Many (m:n)
20
Q
Give an example of each type of relationship.
1: 1
1: m
m: n
A
Husband + Wife
Mother + Kids
A Level Students + Subjects
21
Q
What does a data model include?
A
Entity, attribute + relationship. Usually diagram of database.
22
Q
What is an ER diagram?
A
ENTITY-RELATIONSHIP Diagram. Diagrammatical way of representing relationships between entities in database.
23
Q
What is a foreign key?
A
Primary key from 1 table which is used as link in another table.
24
Q
What is a composite key?
A
2+ attributes used together to uniquely identify 1 record in a table.
25
What are the 3 types of keys?
Primary, Foreign + Composite.
26
What does referential integrity do?
Uses foreign key + its primary key to ensure there are no orphan records (alerts you + can't delete related records). Can be used to cascade changes made to database. Ensures data in 1 table doesn't contradict that in another. Foreign keys must have matching primary keys.
27
What does each foreign key have?
Matching value in corresponding primary key.
28
What is data consistency?
Data stored same in all places where it's recorded.
29
What is data inconsistency?
Not all data stored same in all places where it's recorded.
30
What is data integrity?
Accuracy + correctness of data stored.
31
What is data redundancy?
Same data stored more than once unnecessarily.
32
What is data independence?
Data not being dependent on structure of program used.
33
What is normalisation?
Step by step process for analysing data into constituent entities + attributes. Improves database efficiency. Uses set of rules to structure tables.
34
What are the outcomes when data is normalised?
No data unnecessarily duplicated, data consistent throughout database, structure of tables flexible so data can be added/removed + structure should enable complex queries involving related tables. Reduces update anomalies such as insertion, deletion + modification.
35
What is the purpose of normalisation?
Ensure data is recorded once only to avoid data duplication + inconsistency.
36
What are the 3 stages of normalisation?
First, second + third normal form.
37
Describe each stage of normalisation.
1NF - Remove Repeating Groups
2NF - Remove attributes only dependent on part of comp/prim key (partial dependency)
3NF - Remove attributes not dependent on primary key (Non key dependencies) No transitive dependencies.
38
What are the advantages of a relational database over a flat file?
Data stored once, complex queries can be carried out, less space, faster, better security + cater for future requirements, avoids inconsistent records, easier to change data + data format, data easy to add/remove. Less program-data dependencies (file format specified in each group), more productive maintenance + fewer data sharing issues.
39
What are the advantages of not sharing data multiple times?
No multiple record changes needed, more efficient storage, easy to delete/mod details + all records in other tables which have link to that entry show the edit.
40
Why can complex queries be carried out in a relational database?
SQL. Allows programmers to update, insert, delete, create + drop table records. Actions further refined by where clause.
41
Why does a relational database have better security?
By splitting data into tables, some tables can be confidential (user + pass)
42
How can a relationship database cater for future requirements?
Simpler to add records which aren't yet needed but may be in future.
43
What are the disadvantages of a flat file?
Data inconsistency, data duplication, program-data dependence, less productive maintenance + issues sharing data.
44
What is a data dictionary?
Table which contains descriptions of the structure of data in tables in database (field names, data types, primary keys, how linked) Can be used to provide metadata.
45
What does a data dictionary contain?
Names of current tables + columns, characteristics of each data item (length, type), restrictions of values of certain columns, meaning of data field which isn't self evident, relationships between data items + how program interacts with data.
46
What is a QBE?
Query By Example. Name given to way we set up queries in Access.
47
What is search criteria?
Conditions which ensure only data which passes criteria are displayed.
48
What is SQL?
Structured Query Language. Data access lang which deals with definition + query of database tables + manipulation of tables of data. Can be used to create tables retrieve, update, insert + delete data in rel database. Allows portability as it can be transferred between relational databases easily. ANSI standard.
49
How do you create a table in SQL?
CREATE TABLE.
| Need to identify table name, column names, datatypes of each column + constraints on columns.
50
What are constraints?
Rules enforced on data columns of table. Used to limit data types which can be entered. Ensures accuracy + reliability of data in database. Control data integrity.
51
What are some common constraints?
NOT NULL, DEFAULT, UNIQUE, PRIMARY, FOREIGN, CHECK + INDEX.
52
```
Define these constraints:
NOT NULL Constraint
DEFAULT Constraint
UNIQUE Constraint
PRIMARY Key
```
Ensures column can't have null value.
Provides default value for column when none specified.
Ensures all values in column different.
Uniquely identifies each row/record in database table.
53
Define these constraints:
FOREIGN Key
CHECK Constraint
INDEX
Uniquely identifies row/record in any given table.
Ensures all values in column satisfy certain conditions.
Used to create + retrieve data from database quickly.
54
```
What are the names for these actions in SQL:
Retrieve
Update
Insert
Delete
```
SELECT
UPDATE...SET...WHERE
INSERT INTO
DELETE
55
What is the update query used for in SQL?
Modify existing table records. Use SET to give new value, WHERE to update selected rows.
56
How do you create an insert query in SQL?
INSERT INTO table (entity, entity)
| VALUES (data, data,)
57
How do you use the delete query in SQL?
DELETE FROM table
| WHERE column = data;
58
What are the advantages of QBE over SQL?
Easier to learn, don't have to remember names of attributes as displayed on 1 template, user fills in necessary details, user friendly.
59
What are the advantages of SQL over QBE?
Linear style language + more powerful as QBE can only retrieve data from database whilst SQL can create, alter, delete records + tables.
60
What are access rights?
Correspond to applications which can be performed using SQL. Assigned to diff users depending on their role. (SELECT, INSERT, DELETE, ALTER, CREATE)
61
What is an entity occurrence?
Instance of an entity. aka Record
62
What does cardinality show?
Max num of occurrences which can be involved in a relationship. (1:m, 1:1, m:n)
63
What is database design about?
Identifying the relationships between data + creating logical design best suited to data required for system.
64
What are the steps for creating an ER diagram?
Create box for each entity, draw lines to connect related entities, label relationships using verbs, use Crows Foot Notation to illustrate cardinality of relationships, identify attributes within important entities, remove any many to many relationships which cannot exist in access.
65
How do you write data in UNF?
Unnormalised form. Select name for main entity, each field in brackets, primary keys underlined, foreign keys with *, repeating groups are within {}, derived attributes not included here.
66
What is 1NF?
When all attributes are atomic (can't be further divided) + entities don't have repeating groups.
67
What is a transitive dependency?
When attribute depends on other attribute which isn't primary key.
68
What are the disadvantages of normalisation?
Complex, more tables, slower queries, more relationships to interact with more tables, setting up queries more complex.
69
What is ANSI?
American National Standards Institute.
70
How do you explain the link between 2 entities in an ER?
Entity creates/has/produces/holds/uses/is in entity.
71
What is a repeating group?
The group which has variable info. e.g. pupil has multiple subjects and subjects have multiple pupils. Subject is repeating group. Teacher + room also count.
72
List 5 items which may be contained in a data dictionary.
Field name, data type, description, length + validation.
73
What are 2 advantages of normalisation?
Data redundancy reduced so minimises storage requirement. Data inconsistency reduced so data integrity increased.
74
Explain why a librarian might use QBE to create a query?
Can enter search critera by selecting options from list or typing in own criteria into textbox. Requires less expertise as SQL syntax complicated.
75
What is metadata?
Data about data.
76
State 2 conditions necessary for database table to be in 2NF.
No repeating groups + no partial dependencies.
77
What is data duplication?
Data repeated multiple times in diff places unnecessarily.
78
Compare + contrast ER diagrams + normalisation?
ER uses ER diagrams to rep real world objects + their
links whilst normalisation is to organise data in database to reduce data redundancy + data inconsistency + improve data integrity.
Both used in design stage. Both identify entities
+ relationships.
ER modelling is graphical approach to database design whilst normalisation follows set of decomposition rules.
79
What are 4 different methods of changeover?
Parallel running, direct changeover, pilot running + phased changeover.
80
What is parallel running?
New + old system run side by side until new system has proved itself. Old system discarded when new system satisfactory.
81
What is direct changeover?
New system replaces old system in one step.
82
What is pilot running?
New system used in one section/dept until it's proven, then introduced throughout organisation. Dept that uses new system first is 'pilot site'
83
What is phased changeover?
Changeover in stages. Part implemented, proves itself, next part implemented + so on. Old system continues to perform functions which haven't yet changed.
84
What are the benefits + drawbacks of parallel running?
OG system fully operational if new system fails + results can be compared.
Resource intensive as old + new systems must be kept operational together.
85
What are the benefits + drawbacks of direct changeover?
Old + new system must be resourced during changeover.
| If new system fails, old system not there as backup.
86
What are the benefits + drawbacks of pilot running?
Restricting implementation to pilot site reduces system failure risk + less expensive than parallel.
More expensive than direct changeover.
87
What are the benefits + drawbacks of phased changeover?
Less prone to system failure risk/errors as failures limited to implementation part only + less expensive than parallel.
Can cost more than pilot.
88
What are 3 types of user support?
Hardcopy + electronic user guides, help desk + user groups.
89
Describe hardcopy + electronic user guides.
Printed tutorials + manuals usually available but less common due to printing costs. CD/PDF manuals w/ searchable help index using keyword good. Best for those with some experience.
90
What are some categories of user documentation?
Installation guide, user manual (step by step), searchable index, tutorials, troubleshooting facility + tip of the day.
91
What are some positives and negatives of documentation?
Should cover most aspects of package + easily searchable via index.
Off-putting for inexperienced users.
92
Describe a help desk.
Support phone line for registered users. Best for those with moderate level of experience.
93
What are the positives + negatives of documentation?
Common problems easily solved, may be available 24/7.
| Users not able to describe problem, can be expensive + frustrations with long wait on phone lines.
94
Describe user groups.
Found online for commonly used software. Unofficial groups of users who help each other (email distribution list, online conference). Provide personal contact between new + experienced users where all members can post questions +/or answers. Best for users with a little experience.
95
What are the positives + negatives of user groups?
Common problems easily solved + wide range of knowledge among users.
96
What are 3 categories of ICT resources used for training of employees?
Online courses, interactive DVD, video conferencing.
97
Describe how online courses are used for training.
Take user step by step through common tasks. User works through tasks themselves with test at end. Demonstrations include multimedia elements to show user what they should be doing. Can carry out training simultaneously.
98
Describe how interactive DVDs are used for training.
Video with exercises to follow. Can rewind + watch again. Cheaper than sending users on course. Common. Can interact to choose training they need to complete + feedback test requires user to reach set standard in training. Suitable for all experience levels + can be repeated as often as necessary.
99
Describe how video conferencing is used for training.
Meeting of group of people at num of diff geographical locations. Staff have access to expert at diff location without paying for travel + accommodation. Don't waste time travelling to course.
100
What should be considered when choosing a training method?
Price, user needs (user skills), qualifications awarded?, off the shelf or bespoke software.
