Database Basics and Fundamentals
What are the types of database requests that a DBMS can make and what do they do?
Queries and Updates Queries are retrieval requests from the database and updates are all other requests (create, update, and delete)
What are synonyms for Row?
Record, Row, Tuple
What are the problems associated with storing data in a list?
Redundancy & Multiple Themes
What are common controls/constraints types used to protect the integrity of databases?
Security, concurrency, integrity, and recovery controls.
What is the general process for going from a 1st Normal Form relation to a 2nd Normal Form relation?
Separate each partially dependent attribute and it's key into their own relations resulting in relations that have non-key attributes which are functionally dependent on the primary key. The primary key in the new relations is a foreign key in the old relation that was broken apart.
What is the general process for going from a 2nd Normal Form relation to a 3rd Normal Form relation?
Separate the non-key determinant and it's transitively dependent attributes into their own relation. The non-key determinant because the primary key in the new relation and serves as a foreign key in the now broken apart relation.
What are synonyms for relation?
Table, File, Relation
What are determinants?
The attribute(s) that are used to derive the value of another attribute.
What is the concept of functional dependency?
The idea that one or more attributes, not necessarily from the same entity, can be used to derive the value of another attribute, also not necessarily about any of the entities that the attributes used to derive from it are from.
What is T-SQL?
The language used to administer database services on Microsoft SQL Server.
What is a Database Management System (DBMS)?
The mechanism that interacts with a database within a database system. A DBMS is the only part of the database system that interacts with the database, all other parts must go through the DBMS.
What is a database application?
The mechanism through which a user interacts with the desired data. The database application depends on the data from the database bus it does not interact with it, it interacts with the DBMS.
What is data normalization?
The process of ensuring that data is "well-formed" or that a databases' relations are set up such that when we interact with the data (CRUD), we minimize or eliminate the possibility of anomalies.
What is a primary key?
The selected candidate key will be used to identify entity instances within an entity. A primary key can be a naturally occurring attribute of the entity, an attribute inserted for the purposes of entity instance identification (surrogate key), or a combination of two columns (composite primary key).
What is the formal notion of a database schema?
The structure of a database as codified in a language that is supported by a DBMS. The database is a realization of the language.
What are business themes?
Themes are business concepts that naturally exist in a real life business. Common business themes are employees, projects, & departments.
How do database management systems impact the end goals of a buisness.
They simply serve as a way to make sure your data is good, organized, and can be used for business analysis.
What is the concept of data independence?
Through the separation of the logical database and the physical database, we can make changes to the logical database without actually hanging the physical database. i.e. we can maintain the representation of the physical database while changing the physical database for performance or other purposes.
What is the purpose of security controls?
To ensure database services are limited to users who have been given permission to do so.
What is the purpose of recovery controls?
To ensure that data can be recovered in the event of malfunctions or anomalies.
What is the purpose of integrity controls?
To ensure that the data in the database accurately represents the data that it is meant to capture.
What is the purpose of concurrancy controls?
To moderate the rules around database services that are simultaneously utilized by multiple users in the system.
What are the components of a Database System?
Users Database Applications Database Management System (DBMS) Database
What is the relationship between the parts of a database system?
Users interact with a database application. A database application interacts with a DBMS. A DBMS interacts with the database.
When might a surrogate key be used?
When all the attributes and combinations of attributes for a given entity can not serve as suitable unique keys.
What is a partial dependencies?
When an attribute is functionally determined by parts of a primary key and not the entire primary key.
What is redundancy and how does storing data in a list enable redundancy in data?
When more space than is needed is used to store information. A list requires every row be it's own entity. If you have data that is related to multiple rows, you will need to store the data in every row. As a result, you have stored the data multiple times.
When do you choose the non-standard method of doing something?
When the standard alternative does not provide an important benefit to what you are doing.
What is the advantage of using a relational database?
You can model real business complexions and hierarchies while solving many of the issues that using a list database would cause.
What are the requirements for a table to be a relation under the Relational Model?
- Each row must be an instance of an entity. - Columns contain data about the attributes of the entity. - Cells of a table only hold a single value. - All the values in a column are the same kind of data type. - Every column, within the same table, has a unique name. - The order of the columns and rows is unimportant. - No two rows, in their entirety, can not be identical.
What are the guiding principles of data normalization?
- If a relation is not well-formed, break up the component to create two or more well-formed relations. - A well-formed relation will contain only one theme.
What is a surrogate key?
A column that represents an attribute of an entity that does not naturally occur for the given entity. Surrogate keys are specifically inorganic attributes used for the purposes of entity instance identification and have no meaning outside the database.
What is a key as it relates to the relational model?
A column whose value identifies a row or set of rows.
What is a personal database and what are it's typical characteristics?
A database system that is intended to be used for a single person and not share. Characteristics typically include: - Support only one application - Have only a few tables - Are simple in design - Involve only one computer - Support one user at a time
Compare and contrast non-unique keys and foreign keys?
A foreign key has the characteristics of a non-unique key in that it may identify a set of entity instances within an entity but unlike a non-unique key of an entity, it is not an actual attribute of the entity that is being represented.
What are transitive dependencies?
A functional dependence on an attribute that is not the primary key. A primary key is a determinant for a non-key-attribute which in turn is determinant for one or more non-key attributes.
What is a conceptual schema?
A high level structing of a database schema that attempts to understand the desired applications and end-user needs of a database to be developed.
What is a user in a database system?
A human or computer, that uses the data for a given objective. A user is not interacting with the data for the purposes of managing it.
What is a candidate key?
A key that has the potential to become the primary key. That is to say, it can act as a unique identifier for an entity instance but is not the one that is used for identifying the entity instance.
What is a composite key?
A key that us made up of two attributes that when put together ensure uniqueness. Individually, the attributes do not guarantee uniqueness.
What's the difference between a phyiscal database and a logical database?
A logical database is the business themes and concepts we understand as users, that the data represents. The physical database is the physically stored data that the DBMS interacts with.
What is the purpose of the relational model?
A model for organizing data in a database such that end users can access it using declarative methods
Distributed Application
A network of computers that simultaneously all host different parts of a total application. The entirety of the different parts of the network make up the application.
What is a referential integrity constraint?
A parameter that upholds data integrity by making requiring that data entered in a table matches data in another table by matching a foreign key and primary key in another entity.
How do relational database management systems that use SQL represent a relation?
A relation is represented by the attributes (columns) and entity instances (rows) of table.
What is the first normal form? (1NF)
A relation must have no multivalued attributes. i.e. there is only one value for all attributes for a given entity instance. By definition, all relations are in the 1st Normal Form.
What is a database?
A self-describing organized collection of related data.
Database Server
A server that provides database services (database access, management, manipulation, analysis, etc. ) for clients (other computers) as it relates to the client-server model.
What is a constraint in a DBMS?
A set of rules for the interaction of data that are set up to ensure data integrity.
Microsoft SQL Server
A software application for administrating database services on a database.
What is SQL?
A standard language, that is specialized for different domains used for administrating database services.
Client-server Model
A structure of sharing information in a distributed application, made up of "servers", with "clients". The servers provide a resource, or data, to the clients but the clients do not provide anything to the servers.
What does a table represent?
A table represents a specific relation (or business concept/themes) for databases that are structured in a relational model.
What is the relational model?
A theory for data management based in first-order predicate logic and set theory.
What is the difference between a unique key and a non-unique key?
A unique key has a different value for every row within a given column and thus identifies a single row. A non-unique key may have the same value in different rows for a given column and thus may identify a set of rows.
What is the a foreign key and it's purpose?
A unique primary key attribute that in a entity that identifies an entity instance in another table. It is the primary key in it's native entity and a foreign key in the entity that it is added to.
What is the second normal form? 2NF
All non-key attributes should be functionally dependent on the entire primary key, not partial primary keys (partial dependencies).
What are list modification issues and what are the three kinds of modification issues?
Also called anomalies, they are essentially data that is incorrect because of a deletion, update, or insertion.
What is a null value, what does it mean, and what is it not?
An empty cell within a table. Its implications vary between different systems but it is desired that null values are minimized and/or do not occur in data. It's NOT a 0, space, empty string, tab, or any other character
Why are candidate and primary keys considered determinants?
Because they are unique keys, all the attribute value of a given entity instance can be derived from a candidate or primary key.
What are the types of unique keys?
Candidate Key Composite Key Primary Key Surrogate Key
What do the columns and rows of a relational database table represent?
Columns are business concept (entity) attributes and rows are instances of the concept (entity instance).
What are the four things you can do to data in a databases?
Create, Read, Update, Delete (CRUD) or Create, Query, Modify, Delete
What is application metadata?
Data about the database application that accessed data in the database through the DBMS.
What is overhead data?
Data about the operations and activity of the database. Think, a way to understand what's been going on in the database.
What is data integrity?
Data integrity relates to data being more accurate and consistent throughout its life. Data integrity also means that the stored data reflects real-life world reality.
What is metadata?
Data that describes the data stored in a database.
What are matched data values?
Data that is used to draw a relation between sets of tables by matching data values.
What is meant by an instance of an entity?
Each row must be data that represents attributes of one entity that is an occurrence of the overall entity (business concept, table, etc.).
Who invented the relational model?
Edgar F. Codd
What is the more common understanding of a database scheme and what else does it go by?
Entity Relationship Diagram (ERD) The structure of a database as defined by its entities, relationships, and constraints, often depicted in graphical form. Basically, the rules around a database and the different themes that compromise it.
What does ETL stand for and how does it fit into database management?
Extract, Transform, Load. It's the set of produces to extract data from data sources, manipulates it, and loads it into the end database.
What are synonyms for Columns?
Field, Column, Attribute
What is the issue with Lists and Business themes?
If you want to record multiple business concepts and relate them in a database that uses lists you have to have them on the same list. This causes redundancy and opens up the database to data anomalies.
What is an entity, as it relates to the entity-relationship model?
It is anything of importance that needs to be represented with a single table. Entities are limited to things that can be represented by a single table. In business, it entities are the organic and inorganic themes and concepts that arise from business operations.
What theory is SQL based on?
It uses the relational model of data manipulation and management to interact with databases.
Examples of Database Management Systems
Microsoft SQL Server MySQL Oracle PostgreSQL