Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
137 Cards in this Set
- Front
- Back
|
What is the flat-file system on relational databases? |
- Ordinary files made by host OS - Have no information linking them together, instead application must link the files - Data redundancy is arranged by entirely by users themselves |
|
|
What are the 4 elements required for the creation of a database model? |
- Data - Hardware for storage of data - Software to manage hardware - Users to use the software |
|
|
What are the main elements of a hierarchical database model? |
- Evolutionary file system, directories and subdirectories - Tree structure - Pointers used to traverse database - Parent-child relationships between data - 1 to many representation very good
|
|
|
What are the main elements of the network database model? What database model is it similar to? |
- Similar to heirarchical model - But allows a child file to have more that one parent file - Many to many representation |
|
|
What is the relational database model? |
- Flexible as there are no pre-defined pointers - Relates records using their data alone - Uses foreign keys to identify links between records |
|
|
What type of database model does Oracel SQL use? |
Relational model |
|
|
What type of database model to MySQL use? |
Relational model |
|
|
What are rows composed of in the relational database model? |
Records of data, aka Tuples |
|
|
What are columns composed of in the relational database model? |
Fields or attribute types, to represent data |
|
|
What is an attribute type in the relational database model? |
A type of field, such as account number or first name |
|
|
What is an attribute occurence in the relational database model? |
A peice of data, such as an account balance |
|
|
What is a tuple in the relational database model? |
A record of data, i.e. an entire row showing the data from all columns, such as this account number, has this balance, at this branch |
|
|
What is a table type in the relational database model? |
It is the definition of what attributes are in a table, it also defines the primary key of the table |
|
|
What is the domain in the relational database model? |
Paramteres that can be put in to a column - I.e. VARCHAR2 or NUMBER |
|
|
What is the cardinality of a relational database? |
All rows attributed to a particular column. |
|
|
What is the degree of a relational database? |
The number of columns in a table |
|
|
What is a repeating group in a relational database? |
The exact same data occurring several times in different attribute occurrences |
|
|
What are relations in a relational database? What are they also known as? |
- The structure in which data is stored - AKA tables |
|
|
What is an attribute occurence in the relational database model? |
- It is an entry of data or a value |
|
|
How is a table occurence (extension) illustrated? |
As a table, with example attribute occurences, cardinality and tuples |
|
|
How is a table type (Intension) illustrated? |
Written description, starting with the relation itself, and then the primary key and all the domains afterward in parenthesis: - e.g. Account (AccNo, Balance, Type, BranchNo) |
|
|
What is a primiary key composed of? |
Attrribute, or set of attributes that uniquely identify the each row in a table. |
|
|
What are the 2 main rules for a primary key? |
1) A primary key attribute must not occur more than once in a relation (table) 2) It cannot be NULL. |
|
|
How are primiary keys enforced in a relational database when a table is created? |
Using the CONSTRAINT option underneath the row definitions, something like: - CONSTRAINT Account_PK PRIMARY KEY (AccNo) |
|
|
What can occur if a primary key is not enforced? What problem does this cause? |
- Duplicate rows can occur - This causes relational errors |
|
|
What are foreign keys? |
It is the primary key of one table that is posted to a foreign table to reference data in the foreign tables columns. |
|
|
Are primiary keys uniquely identifiable when they are posted to a foreign table as a foreign key? |
No |
|
|
What are the 4 rules of Relational Tables? |
1) Ordering of rows is not significant 2) Ordering of columns is not significant 3) A cell contains only one value 4) No duplicate rows |
|
|
What is an artificial primary key? |
- Either a fabricated primary key created by the database designer, e.g. FilmId 1, 2, 3 etc... - Or numbers that are accepted by the people in the organisation, e.g. StudentNo P12345678 etc... |
|
|
What is a composite key? |
A primary key that is composed of two or more attributes, that when combined are always unique occurrences. e.g. - Bank (AccNo, Lastname, balance) |
|
|
What is the rule of a composite key? |
All other attrributes in the table must be determined by the whole composite key, not just part of it. |
|
|
What is NULL in a database? |
It is an entry in a cell that is not equal to anything, including itself. |
|
|
Can a primary key contain NULL if it is enforced by oracle using CONSTRAINT? |
No |
|
|
What must foreign key values be equal to? |
Either: - Equal to some value of the primary key - Entirely NULL |
|
|
What is the dependancy that a child has on its parent in relational databases? |
Child is dependant on parent for its primary key. |
|
|
What does the Referential Integrity do to protect a parent table from a child table, and how? |
Protects from new insertion or update into child table without this new value first being inserted into the parent table - Enforced using the CONSTRAINT and REFERENCES commands in table creation. |
|
|
What does Referential Integrtiy do to protect a child table from a parent table, and how? |
It protects the child table from having foreign keys that do not have a value in the parent table. - Usually enforced using ON DELETE SET NULL command in database creation. |
|
|
What are the 3 main ways to enforce referntial integrity on a parent table? |
- FOREIGN KEY CONSTRAINT only - ON DELETE CASCADE - ON DELETE SET NULL |
|
|
What are the characteristics of an Entity in database design? |
Anything that can be named, and that is relevant to the database being desinged and for the users of the database once it is created. - e.g. Customer |
|
|
What is an individual entity in a database known as? |
An ocurrence of an entity |
|
|
How is an entity represented in a pen and paper database design? |
A rectangle with the name of the entity in the middle. |
|
|
What is an attribute in database design? |
A smaller unit of information that combined with all other attributes identify an entity, in the example of the 'customer' entity: - Firstname, Lastname, Post Code, Contact Tel. |
|
|
What is the difference between an entity type and an entity occurence? |
- The type identifies the entity as an overarching definition of itself, e.g. TV Show - The occurrence is an entry in the table with attributes of the entity type, e.g. Writer, Director, Animator |
|
|
What is the difference between attribute type and occurrence? |
- The type identifies the specific small unit of information as an element,e.g. Submarine Colour - The occurrence is a data entry in the column defined by the attribute type e.g. Yellow |
|
|
What entity types could be used if you are designing a database for the Planet Express devliery company? (You do not need to get all of these) |
Events: - Flight/launch - Order - Payment Roles: - Ships captain - Engineer - Foul-mouthed, metal-bending robot |
|
|
When you identify entities in database design, what 3 things should you consider? |
1) Does this entity need to be known to the users of the database? 2) Is there more than one of this entity? 3) Is this entity identified sufficiently elsewhere in the database design? |
|
|
What are relationships between entities? |
An association that both entities share, i.e. - Customer withdraws money from account - Employee assigned department - Godzilla fights in monster smackdown 2K17 |
|
|
How are relationships between entities dipicted in a pen and paper database design? |
A diamond with the relationship in the middle. |
|
|
Are both directions of an entity relationship equally as important? |
Yes |
|
|
What are the main rules for entity relationships in database design? |
- Try not to use 'has' as the relationship, as it is too general, try to be more verbose - Try to begin the description of the relationship with 'a', i.e. 'a university is...' |
|
|
What does a dot on the inside of an entity type indicate in database design? |
Every one of a the entity type all participate in the link to another entity type e.g. - A dot inside the pirate entity box, means that all pirate ships participate in sailing in the ocean. |
|
|
What does a dot on the outside of an entity type indicate in deatabase design? |
Some of the entity occurences of this entity type are not assigned a link, e.g. - A dot on the outside of the ocean entity box means that some pirate ships are not currently sailing in the ocean. |
|
|
What does a number 1 outside the entity box in database design indicate? |
Indicates the maximum degree of 1 assignment for the entity. - For example, a pirate ship can be assigned a maximum of one captain. |
|
|
What does a 'many' or 'm' on the outside of an entity box in database design indicate? |
Indicates there are many links between the starting entity type and the destination entity type, e.g. - Many pirate ships can fly the same jolly roger |
|
|
What are the main elements of the Howe/Chen notation of database design? |
- Dots on inside or outside of entity boxes - Relationships are symbolised by diamonds - Number or 'many' is placed on outside of entity box to indicate whether there is just one, or many links from the source entity type and destination entity type. |
|
|
What are multiplicity questions and what do they establish? |
They are binary questions, with yes/no or 1/many answers. - They establish the validity of the relationship created in a relational database design. |
|
|
What are some example multiplicity questions for this relationship: Dragon is assigned rider? |
- Is every dragon assigned a rider? - Is every rider assigned to one dragon? - How many riders can be assigned to one dragon, 1 or many? - How many dragons can be assigned to one rider, 1 or many? |
|
|
What enterprise rule is applied to a 1 at the destination entity box? |
The source entity must be assigned zero or one of the destination entity, e.g. - A spaceship must be assigned at most one, and only one warp-drive. (or else you'd be warpin' all over the place) |
|
|
What enterprise rule is applied to a 'many' outside the destination entity box? |
The source entity might be assigned to 0, 1 or many of the destination entity e.g. - A YouTube channel may have 0, 1 or many subscribers. |
|
|
What happens when you 'post' a foreign key? |
You check that the rules set up in the database design apply in a dry run of a select statement, e.g. - A car is assigned exactly one engine size |
|
|
Is it possible to post to a destination where there are NULL values in the foreign keys referencing column? |
No, if a database is set up correctly, it should not allow this to happen in the first place. |
|
|
Why must many to many relationships be decomposed? |
Because posting a foreign key does not work in either direction |
|
|
What is created when a many to many relationship is decomposed? |
A middle entity. |
|
|
What does a middle entity in the decomposition of many to many relationships allow? |
It allows the posting of foreign keys through itself correctly. |
|
|
What should a middle entity create in terms of entity relationships when it is implemented in many to many decompostion? |
It should create a 1 to many relationship between itself and the 2 original entities that had a many to many relationship. |
|
|
Where can the database designer draw inspiration from when attempting to name a middle entity for many to many decomposition? |
They can use the 2 foreign keys from the entities either side of the middle entity, e.g. - TechnicianID on left entity - ComputerID on right entity - Combine both together to create middle entity, perhaps try naming it tech support jobs, with composite key (technicianid, computerid,) |
|
|
What should the database designer do if there is a many to many relationship that, upon decomposition, creates a middle entity that already exists elsewhere in the database design? |
- The many to many relationship in question should be fully rejected - The 2 entities that were originally connected with the many to many relationship, are now referenced through their neighbouring entities instead. |
|
|
What problem does a fan trap cause? |
It causes ambiguity. |
|
|
What conditions create a fan trap? |
If navigation goes through a many to 1 relationship, and then a 1 to many relationship. (M:1->Middle-entity->1:M) |
|
|
What problem does a chasm trap cause? |
It causes some tuples to be inaccessible if there is no linking entity between them. |
|
|
What can be done to create safe navigation? |
Eliminate entities causing fan and chasm traps. |
|
|
What is a redundant relationship? |
A relationship that has the same Chen-Ho dot notation as another relationship going to the same destination entity. |
|
|
What does the internal level of a DBMS (Database Management System) handle? |
How data is stored and accessed. |
|
|
What does the conceptual level of a DBMS (Database Management System) handle?
|
What data is available in the database. |
|
|
What does the external level of a DBMS (Database Management System) handle?
|
Limited and simplified views of the database for a single application or a group of applications. |
|
|
What are the 3 main security controls implemented in a DBMS (Database Management System)? |
Authentication Authorisation Accounting |
|
|
What is a database schema? |
A description of a database in terms that DBMS can understand. |
|
|
What are external schemas suitable for? |
Suitable for applications or groups of related applications to query the database |
|
|
What do external schema hide from applications and users? Why? |
- They hide parts of the database that are not required for a particular purpose or department - They hide this to limit the perceived complexity of a large database. |
|
|
What do conceptual schema describe? |
- A global view of the database in a logical form - The global restraints on data |
|
|
What do internal schema describe? |
A global view of the data in technical terms |
|
|
What is data independence? |
The ability to change schema at one level of the database without having to change the schema at a higher level. |
|
|
What schema does logical data independence protect? |
It protects external schema |
|
|
What are the main functions of the conceptual schema? (Try to get 3 of these) |
- Create and drop tables - Set privileges - Alter tables - Set up constraints for P-Keys and F-Keys - Set up ranges in data |
|
|
What are the main functions of the internal schema? (Try to get 3 of these) |
- Data compression - Memory mapping - Index/inode creation and administration - Hash table storage |
|
|
What schema does physical data independence protect? |
It protects conceptual & external schema and application programs |
|
|
What is the result of an SQL command known as? |
A view |
|
|
What is a horizontal view restriction? |
A view which is restricted to a certain number of rows in the table it queries |
|
|
What is a vertical view restriction? |
A view which is restricted to a certain number of columns in the table it queries. |
|
|
How do you drop a view from the current users account? |
drop view name_of_view; |
|
|
If the tables behind a view are updated, is the view automatically updated? |
Yes |
|
|
If the view is updated, are the tables behind it also updated? |
Yes, unless: - The view involves multiple tables - The view has a GROUP BY clause - The user doesn't have sufficient privileges to update their views. |
|
|
How would you use a view called 'my_view' in a select statement? |
select * from my_view; |
|
|
How are users passwords for a database stored? |
They are hashed, then stored in the user database |
|
|
What is the weakness of MySQL's use of hashed password storage? How is this mitigated? |
Can be cracked by rainbow table attacks - Password salting |
|
|
What can an SQL injection attack return? |
Unauthorised data |
|
|
How is authorisation implemented in MySQL? |
Different users are granted different privileges to access and manipulate tables and views |
|
|
What 4 privileges can be granted to any user by the administrator? |
- Select - Update - Delete - Insert |
|
|
What commands are used to add and remove privileges? |
Grant - add privilege Revoke- remove privilege |
|
|
What are the unique abilities of a database administrator? |
- Start and stop database server - Create and drop databases - Allocate user and developer accounts |
|
|
What is the main role of a database developer? What privileges do they have? |
Role: Define how database is structured Privileges: - Create and drop tables - Grant privileges to users |
|
|
What is the main role of database user? Who grants them access to databases? |
Role: Manipulate data tables Privileges granted by developer |
|
|
What are the main functions performed by the accounting module of a DBMS? |
- Monitor authorised/unauthorised usage - Create audit trail of data discrepancies - Restrict access to audit trail |
|
|
Does the exactly one rule provide safe navigation? |
Yes |
|
|
Which direction does posting go in an exactly-one to many relationship? |
From the entity near the one, to the entity near the many with the dot on the inside.
|
|
|
Do non-exactly-one relationships guarantee safe navigation? |
No |
|
|
What direction does posting go in an exactly-one to zero-one relationship? |
From the entity with the dot on the outside, to the entity with the dot on the inside |
|
|
Do zero-one to zero-one relationships guarantee safe navigation? |
No |
|
|
What happens when posting is performed in a zero-one to zero-one relationship? |
Nulls are produced |
|
|
What must be done to many to many relationships? |
They must be decomposed |
|
|
Can a relationship with an exactly one rule be safely navigated? |
Yes |
|
|
What is required in a 1:M or 1:1 relationship where all rules include a zero or many? |
A third table to post to. |
|
|
What can be done with a relationship with 2 exactly one rules? |
They can be merged into one entity |
|
|
What is a recursive relationship? |
Entity that relates to itself |
|
|
What must all tables have when converted from an ERD? |
Unique identifier or key field |
|
|
What is a repeating group? |
Where the one or more cells repeat themselves in a result table. |
|
|
What 2 methods can be used to eliminate repeating groups? |
- Create a new table, that specifically relates the 2 repeating entities uniquely. - Create a composite key that is unique to each entry in the results table |
|
|
What is required for first normal form (1NF) to be complete? |
- Having a key that uniquely identifies the rows (primary key) - This removes repeating groups |
|
|
What is required for second normal form (2NF) to be complete? |
- All pre-requisites of 1NF - Remove functional dependencies |
|
|
What must be assessed during normalisation on the second normal form with a composite key? |
If some attributes only require a partial part of the composite key, then they should be broken out into a new table. |
|
|
What is transitive functional dependency? |
When an attribute is determined by a non-key attribute |
|
|
What is a transaction on a database? |
A logical unit of work that consists of the execution of application or user specified operations. |
|
|
What happens when a database transaction fails? |
Database is left in an inconsistent state |
|
|
What is the Atomicity property of a database transaction? |
An 'all or nothing' unit, either executed in its entirety or not at all - I.e. all Knights must be present at the Round Table, or the Round Table does not meet at all. |
|
|
What is the consistency property of a database transaction? |
Transforms database from one consistent state to another consistent state. - I.e. Optimus Prime going from a consistent badass robot, into a consistent badass truck |
|
|
What is the isolation property of a database transaction? |
Transaction executes independently of other transactions in the database. - I.e. Batman fights crime, independent of the transactions of crime fighting done by the Gotham City Police Department |
|
|
What is the durability property of a database transaction? |
The effects of a successful transaction, permanently recorded in the database. They cannot be lost or discarded. - I.e. JFK makes a successful speech, it is forever recorded on film to be remembered and is not discarded. |
|
|
What happens when a database transaction completes successfully? |
Data is committed to the database |
|
|
What happens when a database transaction completes unsuccessfully? |
Data is rolled back to restore database to previous consistent state. |
|
|
What do each of these commands define in a database transaction? - Start transaction - Commit - Rollback |
- Begin transaction - Make changes to database permanent - Undo effects of transaction |
|
|
What do database log files record about database transactions? What transaction functions use them and what for? |
- Before state of each transaction, for the rollback function to use if needed - After state of each transaction, for the commit function to use to save the changes |
|
|
What is concurrency control? |
Managing simultaneous transactions on the database, without interference from one another |
|
|
What are the 3 main problems with concurrency control? |
- Lost update - Uncommitted dependency - Inconsistent analysis |
|
|
What is the serialisation principle designed to tackle? How? |
- Concurrency problems - Ensures that two transactions operating in parallel have the same effect as they would executing in a serial manner (sequentially) |
|
|
What are the 2 types of transaction lock and what do they do? |
- Locks prevent access to other transactions - Shared read lock (S-lock): shared reading, no writing - Exclusive write lock (X-lock): Exclusive access |
|
|
What are the 2 phases of the locking process? |
Growing phase - Acquire locks but dont release any Shrinking phase - Release locks but don't acquire any |
|
|
What happens in a transaction deadlock? What mitigates a deadlock and how? |
- 2 or more transaction are in simultaneous wait state, waiting for locks held by each other - Mitigated by a lock manager, rolls back one transaction and releases its locks for the other |
