Chapter 1: Databases and Database Design Flashcards
What is a DBMS?
Database Management System.
A software that defines, creates, uses, and maintains a database. Examples include MySQL, Oracle, Microsoft Access
What is the File-Based Approach?
The File-Based Approach was a data management approach in the early days of computing, in which every application stored its data in its own dedicated files, which resulted in multiple instances of duplicate data or data with errors
What is a Database?
A database is a collection of related data items within a specific business process or problem setting
What is Concurrency Control?
Concurrency control is the procedure in DBMS for managing simultaneous operations without conflicting with each another
What is the Database Approach?
The Database Approach an approach to managing data where all data are stored and managed centrally by a DBMS. Applications interface directly with the DBMS, not their own files
What are the two types of data a DBMS stores?
Raw Data and Metadata
What is Metadata?
Metadata is data about data. Its everything that isn’t raw data that you imported. It is typically data definitions that are stored in the DBMS catalog.
It refers to information on schemas and all the other information regarding access, storage, built in programs or any other information about database elements
What makes the Database Approach superior to the File-Based Approach?
Advantages of DBMS over File system:
- Eliminates data redundancy and inconsistency
- Data is easily shared
- Locking system enables data concurrency
- Provides easy data searching
- Ensures data integrity
- Makes for easier data security
What is a Database State?
Database State represents the data in the database at a particular moment. The state changes frequently
What is a Database Model?
Database Model refers to the logical structure, representation or layout of a database and how the data will be stored, managed and processed within it
What is the Three-Layer Architecture?
Three-Layer Architecture is a description of how the underlying data models of a database are related. The three layers are external, conceptual/logical, and internal
What are the three layers of a Three-Layer Architecture?
- External
- Conceptual/logical
- Internal
What is an External Data Model?
Comprised of “views”. The customer facing data model, containing various subsets of the data items in a logical model, tailored towards the needs of specific applications or groups of users
What is a View and what is it used for?
A View is the part of a database that a particular application or user group is interested in, hiding the rest of the database. It is used to control data access and enforce security
What is a Conceptual Data Model?
The model that lays out a description of data items with their characteristics and relationships. Used as a bridge between business users and the database designer/manager
What is a Logical Data Model?
A translation of the conceptual data model towards a specific implementation environment. It can be further translated to an internal data model that represents the data’s physical storage details
What is an Internal Data Model?
A specification of how data is actually stored or organized physically. A model of how data is actually stored on a hard drive or can be found
What is a Catalog?
A repository for data definitions generated by the DDL compiler, integrity rules, metadata, and other information such as users, user groups, and statistics about data and storage
What is a DDL?
Data Definition Language
It expresses the database’s external, logical, and internal data models
What is a DML?
Data Manipulation Language
It is used to retrieve, insert, delete, and modify data
What is SQL?
Structured Query Language
It can be used as both the DDL and DML
What is Data Independence?
Data Independence means that changes in data definitions have minimal to no impact on the application using the data. There can be physical or logical data independence
What is Physical Data Independence and how is it guaranteed?
Physical Data Independence implies that neither the applications, views, or logical data model must be changed when changes are made to the data storage specifications in the internal data model.
It is guaranteed by using a DBMS to interface between logical and internal data models
What is Logical Data Independence and how is it guaranteed?
Logical Data Independence implies that the software applications are minimally affected by changes in the conceptual or logical data model. It is saying that if you add a new entity or something in the logical model, the view of an application will still be unchanged.
It is guaranteed by using a DBMS to interface between conceptual/logical and external data models
What is Structured, Unstructured, and Semi-Structured Data?
Structured Data: data that adheres to a pre-defined data model and is straightforward to analyze. Structured data conforms to a tabular format with relationship between the different rows and columns. Excel and SQL databases.
Unstructured Data: data that either does not have a pre-defined data model or is not organized in a pre-defined manner. Can be images, long text, video, etc.
Semi-Structured Data: data that does not reside in a relational database but that have some organizational properties that make it easier to analyze. XML data.
What are Syntactical Rules and Semantic Rules?
Syntactical Rules: specify how the data should be represented and stored. (Ex. CustomerID should be an integer)
Semantic Rules: focus on the correctness or meaning of the data. (Ex. CustomerID must be unique)
What are the three KPIs of a DBMS?
- Response Time (Low)
- Throughput Rate (High)
- Space Utilization (Low)
What are ACID properties?
Used to ensure concurrency control.
Atomicity: transactions are either executed in their entirety or not at all.
Consistency: assures that a transaction brings the database from one consistent state to another.
Isolation: Ensures that the effects of concurrent transactions are the same as if they were executed in isolation.
Durability: Ensures that the database changes declared successful can be made permanent under all circumstances.