Podcast Beta
Questions and Answers
What is the primary role of a database management system (DBMS)?
Which term best describes how users conceptually organize and understand the data?
What does program-data independence ensure in a database system?
Which component holds detailed information about the structure of the database?
Signup and view all the answers
What is a subschema in a database context?
Signup and view all the answers
What is the primary purpose of Data Manipulation Language (DML)?
Signup and view all the answers
What does the foreign key in a relational database signify?
Signup and view all the answers
Which normalization form requires no repeating groups in a relation?
Signup and view all the answers
In database design, what is the logical design phase primarily concerned with?
Signup and view all the answers
What is the purpose of the 'Entity-Relationship (E-R) diagram'?
Signup and view all the answers
What is 'referential integrity' in the context of relational databases?
Signup and view all the answers
What might indicate an 'insert anomaly' in a database?
Signup and view all the answers
Which of the following statements correctly describes the definition of a primary key?
Signup and view all the answers
In the context of systems development life cycle (SDLC), what is typically accomplished during the coding phase?
Signup and view all the answers
What does the process of normalization in database design aim to achieve?
Signup and view all the answers
What does the term 'cardinality' refer to in the context of database relationships?
Signup and view all the answers
Which statement accurately reflects the role of Data Definition Language (DDL)?
Signup and view all the answers
What is a characteristic of an object-oriented database system as compared to a relational database?
Signup and view all the answers
Which of the following is NOT a characteristic of the 'Data Query Language' (DQL)?
Signup and view all the answers
Study Notes
Database Reading Terms
- Database Management System (DBMS): The program that manages and controls access to the database.
- Database System: The combination of the database, DBMS, and application programs that use the database.
- Database Administrator (DBA): The person responsible for the database.
- Logical View: How users & programmers conceptually organize and understand the data.
- Physical View: How and where the data is physically stored (e.g., on disk, tape, or CD-ROM).
- Record Layout: How a record is displayed or laid out in a system.
- Program-Data Independence: The separation of the logical and physical views of data.
- Schema: The logical structure of the database.
- Conceptual-Level Schema: An organization-wide view of the entire database.
- External-Level Schema: Contains individual user's views of portions of the database.
- Subschema: Each individual view described in the external-level schema.
- Internal-Level Schema: A low-level view of the database; describes how data is actually stored.
- Data Dictionary: Contains information about the database's structure.
-
Data Definition Language (DDL):
- Builds the data dictionary.
- Initializes or creates the database.
- Describes the logical views for each individual user/programmer.
- Specifies security limitations or constraints on database records or fields.
- Data Manipulation Language (DML): Used for data maintenance: updating, inserting, and deleting database portions.
- Data Query Language (DQL): Used to interrogate the database.
- Report Writer: Simplifies report creation.
- Data Model: An abstract representation of the contents of a database.
Relational Data Model
- Stores all data in tables called "relations."
- Tuple: Each row in a relation; contains data about a specific instance of the entity represented by that table.
- Entity Integrity Rule: Ensures each row in a relation represents a specific real-world object.
- Foreign Key: An attribute in one table that serves as the primary key of another table.
- Primary Key: Attribute(s) that uniquely identify a specific row in a table.
- Referential Integrity Rule: Every foreign key must be null or have a value matching a primary key in another relation.
- Normalization: A process of following guidelines for designing relational tables to achieve an efficient data storage scheme.
Database Development Stages
- Requirements Analysis: Identifying user information needs, defining the database system's scope, and determining initial hardware/software requirements.
- Conceptual Design: Translating data requirements into a conceptual model of the database.
- Logical Design: Choosing the DBMS type and translating the conceptual model into one that can be implemented in chosen DBMS.
- Physical Design: Converting the logical schema into a model that describes the physical structures and access methods to implement the system using the chosen DBMS package.
Data Modeling
- Data Modeling: Defining a database to accurately represent all aspects of the organization.
-
REA Data Model: A conceptual modeling tool designed to structure the design of AIS databases by focusing on:
- Resources: Things of economic value to the organization.
- Events: Business activities that management wants to collect information about for planning or control purposes.
- Agents: People and organizations participating in events, about whom information is needed for planning, control, and evaluation.
Entity-Relationship (E-R) Diagram
- A method to visualize the schema of a database; shows entities and relationships between them.
-
Cardinality (of a Relationship): Indicates how many occurrences of one entity can be linked to a single occurrence of the other entity in the relationship.
- Minimum Cardinality: The fewest rows that can be involved in the relationship.
- Maximum Cardinality: The largest number of rows that can be involved in the relationship.
- Concatenated Keys: Multiple-attribute primary keys.
Microsoft Access Technology
-
Relational:
- Data in table format.
- Requires less storage space.
- Supports ad-hoc (on-the-fly) queries.
- Limitation: can't handle complex data types.
-
Object-Oriented:
- Data and instructions are encapsulated.
- Code can be reused.
- Can handle complex data types.
- Limitation: Ad-hoc queries are harder to perform.
DBMS Languages
-
DDL (Data Definition Language):
- Builds the data dictionary.
- Creates the database.
- Describes logical views for individual programmers.
- Defines internal controls in the system.
- Used by DBAs and programmers.
-
DML (Data Manipulation Language):
- Inserts, deletes, and updates data in the database.
- Used by DBAs and programmers.
-
DQL (Data Query Language):
- Interrogates the database.
- Retrieves, sorts, orders, and presents database subsets.
- Used by system users to ask queries.
SQL (Structured Query Language)
- A complex language, but powerful with basic syntax:
- Select: Lists columns to display in the response.
- From: Lists tables containing those columns and specifies joins between tables.
- Where: Lists field values to be chosen.
- Order by: Sorts rows in the response table.
- Group by: Groups rows in the response table based on values in chosen columns.
Systems Development Life Cycle (SDLC)
- Stages:
- Planning: Accountants help determine the feasibility of the proposed project.
-
Requirements Analysis:
- Identify user information needs.
-
Design:
- Develop logical and physical models.
- Specify controls.
-
Coding:
- Accountants with IT skills might participate.
-
Implementation:
- Test the accuracy of the new database and any application programs that use it.
-
Operation:
- Use the system to process transactions.
Relational Database Requirements
- Primary keys must be unique.
- Each foreign key must be null or have a value corresponding to a primary key in another relation.
- Every column in a table must describe a characteristic of the object identified by the primary key.
- Each column in a row must have a single value.
- Values in every row of a specific column must be of the same data type.
- Neither column order nor row order is significant.
Enforcement of Referential Integrity
- Every foreign key must either be null or have a value corresponding to a primary key in another relation.
Segregation of Duties
- Separate the execution, recording, and authorization of accounting tasks:
- Prevents employees from altering data for personal gain.
- Prevents employees with extensive program controls knowledge from circumventing controls.
- Prevents employees from authorizing a fraudulent program change and then making the change themselves.
Database Query Processing and Table Joining
- Each operation in a query filters data into smaller and smaller temporary tables, ultimately producing the final answer: the "dynaset."
- Tables are joined using the DBMS and DQL to create the right combination of attributes to find the answer to the query.
Database Normalization
- Purpose: to eliminate data anomalies in a database design.
- Data anomalies:
- Insert anomaly: Inability to insert or add data due to design issues.
- Delete anomaly: Deleting data unintentionally results in the loss of other related data.
- Update anomaly: Changes to a data item need to be performed in multiple places instead of just one.
- Forms of normalization:
-
1st Normal Form (1NF):
- No repeating groups.
- Absence of multiple cell values in non-key attributes for a single primary key.
-
2nd Normal Form (2NF):
- Must be in 1NF.
- Non-key attributes in the relation depend on the entire primary key.
- No transitive dependency (a dependency between non-key attributes or one not originating from the primary key).
-
3rd Normal Form (3NF):
- Must be in 2NF.
- All functional dependencies in the relation stem from the primary key.
- No transitive dependencies.
-
1st Normal Form (1NF):
Database Schema
- Different ways of viewing the database's structure.
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Related Documents
Description
Test your knowledge of essential database concepts with this quiz. Explore key terms such as DBMS, schemas, and views that are vital for understanding database management. This is perfect for students or professionals looking to solidify their database terminology.
