Databases

Question 1

What are databases?

Answer 1

Large collection of data organised for rapid search + retrieval (by comp). E.g. card index (punch cards storing data, like rolodex but in comp lang), flat file (Files store data in hierarchical data structure, analogue of physical storage) or relational (Entities can ref each other, more efficient as reduces dup, quicker search, can be hierarchal but also reps complex relationships).

Question 2

What does a database system involve?

Answer 2

Schema (design/structure of data + how organised), what store + how related), DBMS (Database Management System, software used to store data efficiently, e.g. Access, MySQL), Query Language (domain specific language [DSL] to talk to DBMS, Insert, manipulate, search, extract e.g. SQL) + data itself.

Question 3

What are the 3 levels of design?

Answer 3

Conceptual, logical, physical.

Question 4

What are entities?

Answer 4

People, things, events + locations. Distinct thing with indep existence which we store data about. Logical thing, not specific e.g. dog = entity but Danny = instance of entity.

Question 5

What are entity relationships?

Answer 5

We rep entities + relationships in Entity Relationship Diagram (ERD). Relationship is connection between entities. Cardinality of relationship is numerical relationship (3 flavours of cardinality) i.e. 1:1 (each entity has 1 related entity), 1:n (1 entity may have multiple related entities but entity on other side has only 1) + m:n (both entities can have many related entities)

Question 6

What is an attribute?

Answer 6

Properties/characteristics of an entity, i.e. required/optional, simple/composite, single-valued/multi-valued, stored/derived, keys.
If an attribute is required, it cannot be blank or have a null value.

Question 7

Simple vs Composite Attribute

Answer 7

Simple is self contained e.g. gender, whilst composite is made up of other attributes e.g. address.

Question 8

Single vs Multi-Valued Attribute

Answer 8

Single means only 1 value e.g. DOB. Multi means multiple values e.g. skills.

Question 9

Stored vs Derived Attribute

Answer 9

Stored are data we actually store e.g. DOB whereas derived are derived from stored e.g. age.

Question 10

What is a primary key?

Answer 10

Attribute which uniquely identifies individual instances of an entity, quick way to find instance. Can’t be blank/null. E.g. student ID num.

Question 11

What is a candidate key?

Answer 11

Attribute that could be a key, identified before choosing a primary key.

Question 12

What is a composite key?

Answer 12

Combo of attributes that uniquely identifies individual instances of entity.

Question 13

What does open source mean?

Answer 13

Software source code is provided + can be modified by anyone.

Question 14

What is the difference between MySQL + MariaDB?

Answer 14

It’s a binary replacement with add+ features + dif licencing. MySQL more supported + MariaDB freer + more innovative.

Question 15

What is a NoSQL DBMS?

Answer 15

‘Not Just SQL’ is increasingly pop approach to data storage, moves away from trad model of large tables where state (data consistency) closely controlled to large flexible databases focusing on availability. V. pop for large data + high availability (lots of users) applications. e.g. MongoDB.

Question 16

What is ORM?

Answer 16

Object-Relational Mapping. Mapping of entities within software to entities within database (e.g. class in Java maps to table in database). Often done automatically – tables automatically created in database once code written.

Question 17

What are the main data sources?

Answer 17

Social media platforms, pharmaceuticals, medicine & e-commerce (transport, banking, retail, construction, financial, insurance, energy).

Question 18

What’s wrong with unstructured data?

Answer 18

Hard to use + hard to turn into info.

Question 19

What is hierarchical structure?

Answer 19

Graph (tree-like structure). Each node may have 0+ children, each child has 1 parent, root note has no parent. 1st database model, created by IBM in 60s.
E.g. Windows registry stores low-level settings for Windows OS + apps using registry.

Question 20

What are the pros + cons of hierarchical structure?

Answer 20

Pros: Clean + logical, fast to navigate, little data duplication, everything in place.
Cons: Many to Many not supported, rarely models real world.

Question 21

What is set theory?

Answer 21

More flexible, items of data (nodes) can belong to 0+ sets, sets can contain 0+ items. Like Venn Diagram. Union (either/both), intersection (both) + set diff (\ in one, not other).

Question 22

What is data mining?

Answer 22

Computerised process to discover patterns in data using combo of techniques from data science, machine learning, stats + DB systems. Involves pre-processing, data mining, validation & visualisation.

Question 23

What is pre-processing?

Answer 23

Assembly of data into set + cleansing (removal of noise + fitting in missing data).

Question 24

What is data mining as a process within data mining?

Answer 24

Finding anomalies (Unusual records/interesting errors), association rules (searching for relationships between variables), clustering (groups & objects with similarities), regression (find function/model which fits with least error) & summarisation (compacted/visualised rep/report).

Question 25

What are the issues with data mining?

Answer 25

Suitable available data, privacy concerns, processing power, accuracy of training/baseline data + false patterns/findings, correlation != causation. Data mining IRL involves risk models, fraud detection + trend predictions.

Question 26

What is normalisation?

Answer 26

Organising data into multiple related tables to min data redundancy + improve data integrity.

Question 27

What is data replication?

Answer 27

Increases database size + creates issues with insertion, deletion + updating. Data duplicated unnecessarily.

Question 28

What is un-normalised form?

Answer 28

List of un-normalised attributes showing key field + repeating groups in (…)* Data may be repeated (multiple attributes/columns with same name) + non-atomic (e.g. address). May have no primary key.

Question 29

What is 1NF?

Answer 29

Broken into repeated groups. Identify keys of each group + foreign key links using @, repeating group key will be compound 1 of at least 2 fields. No attributes with repeated/similar data, atomic attributes (e.g. Name -> Forename, Surname). Has primary key, each attribute has unique name.

Question 30

What is 2NF?

Answer 30

Remove partial-key dependencies. Identify keys of each group + foreign key links using @.

Question 31

What is 3NF?

Answer 31

Remove non-key dependencies. All groups have key, foreign links @ clearly shown.

Question 32

What is atomic data?

Answer 32

Data composed to lowest level, attribute that can’t be broken down any further.

Question 33

What is a foreign key?

Answer 33

Attribute, or set of attributes, within 1 relation that matches candidate key of some relation.

Question 34

What is referential integrity?

Answer 34

E.g. Any student added to enrollment must first exist in student.

Question 35

What is a schema?

Answer 35

Standard notation for documenting entity names, attributes + identifiers. i.e. Entity […Attributes…], Primary key underlined. (Attributes) means repeated fields. E.g. Registration [Student_Number, Surname, Forename, Address, Gender, (Module_Code, Module_Title, Lecturer_Code, Lecture)]

Question 36

What are some of the keywords + their meanings in databases? How should attributes be named?

Answer 36

Entity = table, attribute = field, instance of entity = row in table. When naming attributes, remove spaces + don’t use reserved words or special characters.

Question 37

What are the MySQL data types?

Answer 37

MySQL data types: TINYINT (-128 to 127 signed, 0 – 255 unsigned), SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE (larger than float), BOOLEAN, DATE, DATETIME (ms precision), TIMESTAMP (us precision), TIME (us precision), YEAR, CHAR (fixed length of characters), VARCHAR (variable length of characters), TEXT (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT), ENUM (set of options).

Question 38

What are some of the additional details necessary in MySQL regarding data types?

Answer 38

Whether it’s required/can be null, if indexed, if a default value available, if must be unique.

Question 39

What is a data dictionary?

Answer 39

Rep of physical database design.

Question 40

What is SQL?

Answer 40

Domain-specific language (DSL), used in programming + designed for managing data (organising + retrieving info) held in RDBMS. ANSI standard 1986, ISO standard 1987, not entirely portable. Designed to be human-readable.

Question 41

What are some of the key words used in SQL?

Answer 41

CREATE TABLE, ADD INDEX, INSERT, DELETE, UPDATE, SELECT. E.g. SELET name FROM pet WHERE pet id = 40255. Use * for all. Also SUM(), COUNT() + AVG(). Can rename fields e.g. SELECT name AS “Pet Name” FROM Pet. In text, there’s = (exactly equals, case insensitive) or LIKE and % (wildcard), esp for last letter.

Question 42

How do we order SQL results?

Answer 42

ORDER BY field ASC/DESC. Can do multiple fields in order of priority just use comma.

Question 43

How do we limit num of SQL results returned?

Answer 43

We can limit num of fields using LIMIT start, num from start.

Question 44

What is concurrency?

Answer 44

Introduces possibility of wrong info being written, updates overwriting each other. Can use locks in SQL to tell DB we only want to be able to read/write data. Can be done on tables or rows. Similar to feature called transactions (group of statements grouped + executed together which can be rolled back if fail). E.g. LOCK TABLES table READ/WRITE. UNLOCK TABLES.

Question 45

How do READ locks work?

Answer 45

Multiple READ locks can be held by diff sessions. While READ locked, locking session can read data, other sessions can start READ locks + read from tables, other sessions without locks can read. Nobody can write.

Question 46

How do WRITE locks work?

Answer 46

Session holding WRITE lock can read + write from table, only 1 WRITE lock held at time. Nobody else can read/write from table.

Question 47

What is VIEW?

Answer 47

Virtual table (like pre-built query output), used for commonly used outputs + access control, appear as real tables. Once created, stored as part of schema. GRANT access to resource in DB to specific USER for specific OPERATIONS. E.g. GRANT SELECT ON db.table TO user@host IDENTIFIED BY password.

Question 48

Why is db security important?

Answer 48

Commercial sensitivity (financial losses through frauds by employees) + personal privacy + data protection (leaking bad). Key issues: availability, authenticity, integrity, confidentiality.

Question 49

Describe availability in terms of db security.

Answer 49

Data must be available at all times, to appropriate users + must track who has access to it + who has accessed it.

Question 50

Describe authenticity in terms of db security.

Answer 50

Ensure data has been edited by authorised source, ensure users accessing system are who they say they are, verify all report requests are from authorised users + verify any outbound data is going to expected receiver.

Question 51

Describe integrity in terms of db security.

Answer 51

Integrity: Verify external data has correct formatting + other metadata, verify input data accurate + verifiable, ensure data following correct workflow rules for company, be able to report on all data changes + who authored them to ensure compliance with corporate rules + privacy laws.

Question 52

Describe confidentiality in terms of db security.

Answer 52

Ensure confidential data only available to correct people, ensure db is secure from external + internal system breaches, provide for reporting on who has accessed what data + what they’ve done with it. Mission critical + legal sensitive data must be highly secure at risk of lost business + litigation.

Question 53

What are security models?

Answer 53

Access control (method/system module to ensure users are who they say they are + have appropriate access to data e.g. log-in/off, password management. Authentication + authorisation).

Question 54

What is authentication?

Answer 54

DB authentication (process/act of confirming user who is attempting to log into db is authorised to do so + accorded rights to perform only activities they’ve been authorised to do). Authentication is prerequisite for authorisation.

Question 55

What is authorisation?

Answer 55

Process where db manager gets info about authenticated user.

Question 56

What is GRANT?

Answer 56

GRANT ACTION ON db.table TO person IDENTIFIED BY password. WITH GRANT OPTION allows user to pass grant onto other people.

Question 57

What are stored procedures?

Answer 57

Ways of creating code blocks you can reuse. Procedures do task but may not return a value, function does something but can return data. Can provide API (application programming interface for db app), easy maintenance (centralise complex code in single maintainable instance). Used for business logic, can receive + send parameters identified as IN, OUT, INOUT. Can read data from IN parameters + write data via OUT parameters + both from INOUT parameters.

Question 58

What are triggers?

Answer 58

When event occurs, do something. Allows us to check + fix integrity of data at db level, can cascade data creation automatically, can audit changes made, can run events every time change occurs. E.g BEFORE/AFTER INSERT/UPDATE/DELETE