36668152
Description
Flashcards by Malachy Moran-Tun, updated more than 1 year ago
|
|
Created by Malachy Moran-Tun
almost 2 years ago
|
|
| Question | Answer |
| What is an Entity? | A category of object, person, event, or thing of interest to an organisation, about which data is to be recorded |
| What are Attributes and Records? | > Attributes - columns of the whole table (entity) > Records - rows of the whole table |
| What is a Flat File Database? | > Consists of a single file > Suitable to store one entity, e.g., a list of aname > Repeated data makes them inefficient > Unsuitable for more than one entity and their relationships |
| What is a Primary Key? | > Identifyer required for each entity in a database > Usually a numeric key for each record |
| What is a Secondary Key? | Used as an alternative index that is easier to know, e.g., a surname rather than a number |
| What are the 3 Types of Relationships between Entities? | > One-to-One > One-to-Many > Many-to-One |
| What is a Foreign Key? | > Attribute that joins two tables > Common to both tables - primary key in one, foreign in another > Allows for relationships between tables |
| How are Tables Linked in Many-to-Many Relationships? | > Additional table is created, containing just both primary / foreign keys > Both keys in the one table count as a "composite primary key" |
| What is Referential Integrity? | > Keeping shared / required attributes in databases > Shared attributes are not deleted on only one entity, in order to maintain the relationship |
| What is Normalisation? | > Process to ensure the best possible design for a relational database > Ensures that - No data is unnecessarily duplicated - Data is consistent throughout the database (e.g., not conflicting entries in different tables) - The structure of each table is flexible and allows entering as many or as few items as requied - The structure allows for complex queries |
| What is First Normal Form? | > All field (attribute) names must be unique > Values in fields (attributes) should be from the same domain > Values in fields (attributes) should be atomic, i.e., they are either updated in their entirety or not at all > No two records may be identical > Every table must have a primary key |
| What is Second Normal Form? | > All the requirements of 1NF plus: > Remove any partial dependencies (i.e., none of the attributes depend on composite keys / only part of the primary key) > Fix and Many-to-Many relationships as a result of removing partial dependencies |
| What is Third Normal Form? | > All the requirements of 2NF (and 1NF) plus: > Remove any transistive dependencies (i.e., ensure that non-key fields are not dependent on eachother) |
| Why is Normalisation Important? | > Data integrity (i.e., no data redundancy) is maintained, meaning there is no conflicting or unnecessary duplication of data > Faster searching, due to smaller tables with fewer fields (also saving storage space) > Records cannot be accidentally deleted in one-to-many relationships |
| What is Capturing Data, and what Methods can be Used? | > Data has to be inputted (either automatically or manually) before it is added to a database > Manual methods include transcribing data from a form > Automatic methods include: MICR (Magnetic Ink Character Recognition, used for cheques), OMR (Optical Mark Recognition), OCR (fuck 'em... i mean Optical Character Recognition), or NFC (Near Field Communication) > Data may be selected / filtered, only capturing those that match certain criteria before being added to the database |
| How can Data be Transferred between Systems without the need for Human Intervention? | > EDI - Electronic Data Interchange > Documents that use standard formatting can be exchanged electronically > Transaction software processes the information |
| What is a Transaction? | A single, logical operation on data |
| What is ACID? | > A set of properties that guarentee transactions are reliably processed > Atomicity - transaction is processed in its entirety or not at all (e.g., in powercuts or crashes, the transaction does not proceed, or only process part of it) > Consistency - no transaction has violate the defined rules for maintaining referential integrity > Isolation - a transation cannot be interrupted by another, and concurrent transations lead to the same results as transactions happening one after another > Durability - similar to atomicity, once a transaction has been committed, it is irreversable, even if there is a system fail |
| What is Record Locking? | > Prevents simultaneous access to objects in a database, so multiple people cannot update it inconsistently > E.g., a record is locked when a user is editing it or retrieving data from it |
| What is Serialisation? | > Ensures transactions do not overlap in time - i.e., they cannot interfere with eachother, or lead to updates being lost |
| What is Timestamp Ordering? | > Serialisation technique > Objects in a database are given a read and write timestamp, updated when needed > When a transaction starts, it reads data from a record, setting the read timestamp. Before data is written, it checks to see if the timestamp has updated since, if so, it does not continue as a transaction is currently taking place |
| What is Commitment Ordering? | > Serialisation technique > Ensures that transactions are not lost when more than one person is accessing the same database object > Transactions are ordered in terms of their dependencies on eachother, as well as by time |
| What is SQL? | > Structured Query Language > Declaritave language used for updating tables in a relational database > Uses statements to make, query, and modify databases |
| What do the Following Commands do? > SELECT > FROM > WHERE > ORDER BY | > SELECT - lists the fields to be displayed > FROM - lists the table(s) where data will be read from > WHERE - lists data that matches the criteria > ORDER BY - orders the results (default is ascending order) |
| How does the LIKE Command Work? | > Condition that finds values with a wildcard > LIKE "a%" finds values that begin with a > LIKE "%a" finds values that end with a > LIKE "%a%" finds values with a in any position > LIKE "_a%" finds values with a in second position (more underscores = higher position) > LIKE "a%b" finds values that begin with a and and with b |
| What does the JOIN Command do? | > Combines rows from two or more tables, based on a common field between them |
| How does the CREATE TABLE Command Work? | CREATE TABLE table_name ( Attribute1 DATA_TYPE, PRIMARY KEY Attribute2 DATA_TYPE etc. ) For linked tables: FOREIGN KEY Attribute3 REFERENCES OtherTable(Attribute3) |
| What Data Types can be used when Creating a Table? | > CHAR(n) - string of fixed length n > VARCHAR(n) - string of variable length, max n > BOOLEAN - true or false, i mean you should know that one > INTEGER / INT - ... an integer, what did you expect? > FLOAT - a decimal (real) number > DATE - a... a date. y'know, day, month year > TIME - like hours, minutes, and seconds... it's pretty self explanatory > CURRENCY - le cash money |
| How does the ALTER TABLE Command Work? | > Used to add, delete, or modify columns > ALTER TABLE table_name ADD attribute_name - adds the specified attribute > ALTER TABLE table_name DROP COLUMN attribute_name - removes the specified attribute > ALTER TABLE table_name MODIFY COLUMN attribute_name DATA_TYPE - changes the data type of the attribute |
| How does the INSERT INTO Command Work? | > Used to insert a new record into a table INSERT INTO table_name (column1, column2...) VALUES (value1, value2...) > If attributes are unknown, do not list them in the columns or values |
| How does the UPDATE Command Work? | > Used to update a record in a database table UPDATE table_name SET column1 = value1, etc. WHERE attribute = value |
| How does the DELETE Command Work? | > it deletes a record. who'd've thunk it? DELETE FROM table_name WHERE attribute = value |
Want to create your own Flashcards for free with GoConqr? Learn more.