Chapter 7 - Database Fundamentals Flashcards
5.1, 5.2, 5.3
Database
a structured collection of organized data that is stored and managed in a way that allows for efficient retrieval, modification, and analysis of information
- stored locally, on a server, or the cloud
- resembles spreadsheet but contain tables instead
Create
defining what information the database will store, where it will be stored and how the information will be accessed
Input/Import
populating a database with data records
Query
a question or request for specific information from a database and is the way in which we retrieve data stored in the database
Report
visual summary of queried data that is designed to provide a clear, concise, and structured view of the data
Database Records
collections of related data fields that represent a single unit of information or entity within a database. It is often used to store and retrieve specific information about an object or item
Structure Data
data that is highly organized each piece of data has a well-defined data type
- organized into tables
- easy to query and analyze
Unstructured Data
lacks a predefined structure or format it included text, images, video, audio, and other forms of content that do not fit neatly into a table format.
Semi-structured Data
data that doesn’t adhere to organized tables but is not completely unstructured
- included data such as text, images, video, and audio accompanied by various meta tags
Schema
outline or a blueprint for a database that describes its components and how they work
- table names
- fields (attributes or details) in each table
- data types required for each field
- primary key, foreign keys, and other constraints
DDL Commands
SQL commands that manage the structure of your database
When Should We Use A Database?
- Multiple concurrent users
- Scalability
- Speed
- Variety of Data
- Security
Relational Database
structured to recognize relationships among stored items of information, known as datasets.
- predictable and organized with tables containing columns and rows of text or numerical data
Primary Key
is a unique identifier that helps you define a record (or content in a row)
- There can only be one Primary key per record
Relational Database Management Systems (RDBMS)
are software used to manage a database
- add, remove, update records
- retrieve data
- SQL, Oracle, Sybase, Informix, MySQL
Foreign Key
is a key or field in a table that is used to link two tables together
Non-Relational Database
does not use the tabular schema of rows and columns
- uses a storage model that is optimized for the specific requirements of the type of data being stored
- Also known as “NoSQL” databases
**Include Non-structured data & Semi-structured data
Key/Value Databases
represent data as a collection of key/value pairs
- Redia
- Dynamo DB
- Microsoft Azure
- Amazon
- Oracle NoSQL
Database Dumps
one-time logical backup of a database
- Data is dumped into an empty database that is already set up with the schema (fields, tables, etc.)
Database Backup
done through a program and creates a physical backup
- can be scheduled and repeated
- used to restore a database without needing to create a new on
Manual Access
going directly into tables to view or change data, suitable method for working with a database that is stored locally
Direct Access
accessing via a direct connection across a network suitable for LANs
Programming Access
accessing a database through a programming language like SQL
User Interface and Utility Access
A method of accessing a database using forms that allow users to interact with the database that create
an SQL query to retrieve data from the database.
Query/Report Builders
provides a GUI for users to select actions to Pperform on the database
and converts those selections to the SQL statements that will be executed.
Data Manipulation
enables the ability to adjust the content/data in your table
- Select: allows to “select” or specify intended data
- Insert: Allows the ability to add contents of a table
- Delete: Allows the ability to remove contents of a table
- Update: Allows the changing or editing of contents in table. You are not deleting, only editing
Data Definition
“defines” the structure of your database and its tables
- Create: allows to create a new structure, such as a new Database or new table.
- Alter: usually used to alter DB object such as column name
- Drop: allows the removal of a Database or Table(s).
- Permissions: Identifies who had the ability to edit table structure
Flat File Systems
useful for such things as transferring information between systems or for
tracking sales orders, they have several drawbacks.
Concurrent use
- Databases allow many individual users to access the data at the same time
without having to wait or take turns. - A database can support thousands or even millions of users
at one time.
Tables
stores the information in a relational database
- database. Each table is comprised of fields (columns) and
records (rows). - There is no limit to the number of fields and records a table can contain, if there's
enough storage space on the drive.
Records/Rows
a single instance of data stored in a database table
- a database of people, each individual person is a record (row)
Fields/Columns
describes a single attribute of each record in a table
- For example, you may have a field
for first names in a database. Every person’s first name is stored in the first name field. Fields are comparable to columns on a spreadsheet.
Constraints
The schema of a database can prevent some fields from being left blank.
- It requires a value to be entered into the field whenever a new record is
created - Constraints can be placed on
fields, so they only accept certain types of data - data. This is used to avoid what is known as
Garbage In, Garbage Out (GIGO).
