Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Software engineering 2nd yr > Database Principles > Flashcards

Database Principles Flashcards

1
Q

What is a surrogate key and when is it used

A

A single column that you fill with artificial values. Used when a table doesn’t have data that supports creating a natural primary key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is a candidate key

A

A minimal super key that is, a key that does not contain a subset of attributes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is an alternate key

A

A key that contains all the properties needed to become a candidate key. Can be a set of single attributes or multiple attribute

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is a superkey

A

A key that can uniquely identify any row in the table. Time attribute is a good example of a super key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Does the order of creation matter

A

Yes cant create an intersection table cust_order before table customer

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is DDL

A
  • Data Definition Language (DDL)
    Allows the DBA or users to describe and name entities, attributes and relationships required for the applications that access it

Examples
CREATE TABLE, ALTER TABLE, DROP TABLE, RENAME TABLE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is DML

A
  • Data Manipulation Language (DML)
    Provides the ability to manipulate data within the database.

Examples
SELECT, INSERT, UPDATE, DELETE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is a join and how does it work

A

A JOIN links tables by selecting only the rows with common values in their common

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How does inner join work

A

Only returns matched records from the tables that are being joined

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How does outer join work

A

Matched pairs would be retained, and any unmatched values in the other table would be left NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is data control language

A

Language used to control access to data stored in a database

Example
REVOKE
GRANT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is a transaction

A

Any request for a piece of information from a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is transaction analysis

A

the process of ensuring that multiple database operations (like updates or inserts) are done correctly and completely as a single

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is the purpose of TA and what does it allow

A
  • Purpose of transaction analysis is to be able to see which tables are used the most in transactions
  • With this information we can optimize the tables
    It guarantees data consistency and reliability
    . Can help identify problems
    . can improve performance
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What are the 3 main categories of databases

A

○ According to the number of users
○ According to the type of use
○ According to Database site location

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What are 2 categories for users in a database

A

Single User database systems
Multi User database systems

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is a Single User database systems

A

. Database is on the PC and hard disk
. All applications run on the same PC and directly access the database
. A single user accesses the application

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is a Multiuser database systems

A

. many PC’s connected through a LAN
. a file server stores a copy of the database files
. each user runs a copy of the same application and accesses the same files.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What are the 4 categories for type of use in a database

A

Production or Transactional Database system
Data Warehouse
Decision Support Database System
Research

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is Production or Transactional Database system

A

. Used for management of supply chain and for tracking production of items in factories, inventories of items in warehouses/stores and orders for items
. Used for purchases on credit cards and generation of monthly statements
. Used in Banks for customer information, accounts, loans and banking transaction

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is a data warehouse

A

. Historical collection of data and transaction
. Can be used to find trends

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is a Decision Support Database System

A

. Its a hybrid
. Aids users in judgement and choice activities
. Used in environments like business, health care and military where you have to make decisions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What are the 4 categories for database location

A

Centralised database system
Parallel database system
Distributed database system
Client/Server database system

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is a centralised database system

A

Single processor together with its associated data storage devices and other peripherals

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What is a parallel database system

A

○ A database system implemented on a tightly coupled multiprocessor or on a loosely coupled multiprocessor
○ They link multiple smaller machines to achieve the same throughput as a single larger machine

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What is a distributed database system

A

○ Data is spread across a variety of different databases
○ Managed by a variety of DBMS’s that are running on various types of machines having different operating systems
○ Each machine can have is own data and applications, and can access data stored on other machines
○ Each machine acts as a server as well as client

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What is a client/server database system

A

○ Client-server technology instead of centralized system
○ There is a server which acts as a whole data base management system

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Name some concurrency transaction problems

A

The lost update
The uncommitted dependency
The inconsistent analysis

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What is the lost update

A

An apparently successfully completed update operation by one user can be overridden by another user

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

What is the uncommitted dependency

A

occurs when one transaction is allowed to see the intermediate results of another transaction before it has committed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What is the inconsistent analysis

A

○ Occurs when a transaction reads several values from the database but a second transaction updates some of them during the execution of the first
○ For example, a transaction that is summarizing data in a database (for example, totalling balances) will obtain inaccurate results if, while it is executing, other transactions are updating the database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Name some transaction control techniques

A

Timestamping
Serializability
Locking

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What is timestamping

A

○ A concurrency control protocol that orders transactions in such a way that older transactions, transactions with smaller timestamps, get priority in the event of conflict
○ A unique identifier created by the DBMS that indicates the relative starting time of a transaction

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

What is Serializability

A

○ Ensuring that a set of transactions in a database system produce the same results as if they were executed one after the other in some order, even though they may be executed concurrently.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What is locking

A

Prevents deadlock
○ A procedure used to control concurrent access to data. When one transaction is accessing the database, a lock may deny access to other transactions to prevent incorrect results.
○ Ensures serializability of concurrent transactions
§ Shared lock - If a transaction has a shared lock on a data item, it can read the item but not update it.
§ Exclusive Lock - If a transaction has an exclusive lock on a data item, it can both read and update the item.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What is a deadlock

A

A situation arises when two (or more) sessions are waiting to acquire a lock on a shared resource, and none of them can proceed because a second session also has a lock on some other resource that is required by the first session.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

HAVE A LOOK AT RELATIONAL ALGEBRA

A

HAVE A LOOK AT RELATIONAL ALGEBRA

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What are the 3 things we need to understand when starting a database

A
  1. Business rules
    1. Optimisation
    2. The correct data types
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

What do data types do

A
  • This limits violations of data integrity
    • Defines the kinds of values that can be used or stored
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

What do numeric data types do

A

Numeric data types allow us to specify the range of digits for specific values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

What are the differences between char and varchar

A
  • Char is fixed varchar is not
  • They are stored on the disk differently
  • Var char takes 2 bytes per character whereas char only takes 1
  • Char saves more memory
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

What is one way we can optimise databases

A

Using less joins

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

What are the first things we look at when optimising a database

A

ERD
Data
Tables

44
Q

What is an index

A

a logical structure that improves the speed of data retrieval

45
Q

Name some indexes in postgres

A

B Tree
Hash

46
Q

What is the code to create an index

A

○ CREATE INDEX index_name ON table_name(col_name)

47
Q

Do we just apply indexes anywhere

A

You need to be able to identify which column is the most suitable for an index

The best candidates for indexes are columns that you filter on regularly

48
Q

What is a view

A

A view is a virtual table whose contents are defined by a query.
It holds temporary data

49
Q

How do we create a view

A

CREATE VIEW comedies AS
SELECT *
FROM films
WHERE kind = ‘Comedy’;

50
Q

How does explain work

A
  • With the explain keywords we can see the cost and runtime of queries
  • Useful to see if indexes made a difference
51
Q

What type of views are there

A

Standard view - normal one in postgres
Materialized view - cache the result of a complex and expensive query. Useful when you need fast data access often used in data warehouse and business
Recursive view - come in handy when working with self-referential data

52
Q

What is a subquery

A

A query inside a query.
Th order if execution : Does the inner most query first

53
Q

What is a trigger

A
  • A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data manipulation event.
  • A trigger is invoked before or after a data row is inserted, updated, or deleted.
  • A trigger is associated with a database table.
    • A trigger is executed as part of the transaction that triggered it.
54
Q

What can triggers be used for

A
  • Triggers can be used to enforce constraints
  • Triggers can provide warnings
  • Triggers can be used to update table values, insert records in tables, and call other stored procedures.
55
Q

What are the 3 main types of security violation in databases

A
  • Unauthorized modification of data
  • Unauthorized deletion of data
  • Unauthorized reading of data
56
Q

What are the security measures at their given levels to protect the database from violations

A

○ Database system - Have validation to check they’ve entered the correct thing. Prevent them from entering wrong thing
○ Human factor - Apply correct constraints for data to reduce human errors. Take the approach the user is an idiot and cannot be trusted. Users are a major threat to data integrity
○ Operating System - Operating system must be secured to unauthorised access
○ Network - Security within the network e.g. firewalls
Physical Security - e.g. tailgating, blocking USB ports

57
Q

Name and define some types of authorization

A

○ Read access - Only allows you to read data
○ Update access - Only allows you to update
○ Insert access - Only allows the addition of new data not modification
○ Delete access - Only allows deletion of data
○ Index access - Allows the creation and deletion of indexes
○ Alteration access - It allows the addition or deletion of attributes in a relation
○ Resource access - Allows the creation of new relations
○ Drop access - Allows the deletion of relations

58
Q

Should you give users drop access

A

No we try to avoid this

59
Q

What keywords do we use to give and take away users access in a database

A

GRANT and REVOKE

60
Q

What is the principle of least privileges

A

Never give the user more access than necessary to complete a task

61
Q

What is a policy

A

○ Policies are general statements of direction or action that communicate and support DBA goals

62
Q

What is a standard

A

Standards describe the minimum requirements of a given DBA activity; they are more detailed and specific than policies
They are rules/requirements that need to be met

63
Q

What is a procedure in database security

A

○ Procedures are written instructions that describe a series of steps to be followed during the performance of a given activity

64
Q

Give examples of policies ,procedures and standards

A
  • Policies - All user must have a password that needs to be changed every 6 months
  • Standard - The password must have a minimum have of 5 characters
  • Procedures - To create a password follow these steps
65
Q

Name some aggregate functions

A

MAX,MIN,SUM,AVG,COUNT

66
Q

What is the difference between dynamic sql and embedded sql

A
  • Dynamic SQL - adapting based on your input
  • Embedded SQL - Can not give you flexibility outside of what is given.
67
Q

What is the difference between a trigger and a procedure

A
  • Triggers work at a table level e.g. listen for changes in the table and if there’s an insert check if it already exists
  • Procedure is at the database level e.g. if attack is detected encrypt the data
68
Q

What do functions start and end with

A

$$

69
Q

Do we call both functions and procedures

A

No In SQL we CALL procedures but functions are usually parts of select statements

70
Q

What privilege is needed for users to use procedures

A

Execute privilege

71
Q

Name 1 language for procedures and functions

A

PSM

72
Q

What happens in table replication

A

It creates a permanent table with the data your selecting

73
Q

Show how you would replicate a table

A

CREATE TABLE new_table AS
(SELECT col1, col2, col3
FROM
existing_table
WHERE
condition);

74
Q

What is DQL

A

Data Query Language is used for fetching data. E.g. SELECT

75
Q

What is the order of execution of queries

A

1 FROM, JOIN
2 WHERE
3 GROUP BY
4 HAVING
5 SELECT
6 ORDER BY
7 LIMIT

76
Q

What is the difference between having and where

A
  • Having is filtering at the group level
    • Where is filtering at the row level
77
Q

What is the difference between procedural operators and declarative operators

A

○ procedural system, we tell the system what to do (e.g. arithmetic operations, ordering, substring comparison)
○ in a declarative system, we tell it what result we want (e.g. LIKE, DISTINCT, IN, EXISTS etc).

78
Q

What do we always prioritize in a database

A

Security

79
Q

Why do we monitor a database

A

For performance and more importantly security

80
Q

Name a monitoring software for any database

A

PGadmin

81
Q

What is overclocking

A
  • Overclocking generates heat so faster computer means more heat
82
Q

What is a benchmark

A

standardized sets of tasks that help to characterize the performance of database systems
* Benchmarks are needed to find out how fast the database is running

83
Q

What is tuning the performance

A

involves adjusting various parameters and design choices to improve its performance for a specific application

84
Q

Name some performance issues

A
  • Security
  • Long delays and timeouts
  • Queries performing poorly
85
Q

What is a bottle neck

A

Things that you dont have control over that affects the performance of the system e.g.
* You cant control the client or internet

86
Q

What is a transaction manager

A

A Transaction manager is a specialized piece of software that guides the transactions on where to go.

87
Q

What are the 3 ways Tuning is realised in a database:

A

○ Includes the schema and queries and is system independent.
○ Database-system parameters (e.g. as buffer size and checkpointing intervals)
○ Hardware

88
Q

What does the recovery subsystem do

A

Responsible for making sure that the database is restored to the state it was in before the program started executing.

Ensure that the program is resumed from the point at which it was interrupted so that its full effect is recorded in the database

89
Q

What is a full back up

A

Back up everything since the start

90
Q

What is an incremental back up

A

You only back up the most recent changes

91
Q

What is a partial back up

A

You only back up specific things for example you only want to back up orders

92
Q

What are the 3 types of recovery

A

Crash recovery
Disaster recovery
Version recovery
Roll forward recovery

93
Q

What is crash recovery

A

protects a database from being left in an inconsistent, or unusable, state when transactions (also called units of work) are interrupted unexpectedly.

94
Q

What is disaster recovery

A

process to restore a database in the event of a fire, earthquake, vandalism, or other catastrophic events

95
Q

What is version recovery

A

the restoration of a previous version of the database, using an image that was created during a backup operation (roll back)

96
Q

What is roll forward recovery

A

Patterned based. You analyze what went wrong e.g. database crashes when customer pressed purchase

97
Q

What does the log record do

A

Every transaction in the database creates a log record prior to modifying the database
. allow the system to undo changes made by a transaction in the event that the transaction must be aborted
. allow the system also to redo changes made by a transaction if the transaction has committed but the system crashed before those changes could be stored on the disk

98
Q

What is deferred modification

A

If a transaction does not modify the database until it has committed

99
Q

What is immediate modification

A

If database modifications occur while the transaction is still active

100
Q

Name some ethical issues

A

Intellectual property
Copyleft
Privacy

101
Q

What is intellectual property

A

Intellectual property is something that you create using your mind
You own intellectual property if you:
* Created it
* Bought intellectual property rights from the creator or a previous owner
* Have a brand that could be a trade mark, for example, a well-known product name

102
Q

What is copyleft

A

a general method for making a program (or other work) free , and requiring all modified and extended versions of the program to be free as well

103
Q

What is the privacy issue

A

Involves storing data
○ If you don’t need it don’t collect(e.g. dob)
○ You should justify why you need the data

104
Q

Name some privacy legislation

A
  • UK Data protection Act 2018
    • General Data Protection Regulation (GDPR) of European Union
105
Q

Name some of the data protection principles

A

Data is :
○ used fairly, lawfully and transparently
○ used for specified purposes
○ accurate and, where necessary, kept up to date
○ kept for no longer than is necessary
○ handled in a way that ensures appropriate security, including protection against unlawful or unauthorised processing, access, loss, destruction or damage
* There’s stronger protection for things like race, ethnicity, gender, relegion,biometrics and etc

106
Q
A

Software engineering 2nd yr (3 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions