Category 1: Database Basics
What is a database?
A structured repository of information, organized in tables.
What is a primary key?
A unique identifier for each record in a table.
What is the purpose of a table in a database?
To store related data in rows (records) and columns (fields).
What is the purpose of a query in Microsoft Access?
To retrieve, filter, or manipulate data from one or more tables.
A school wants to track student attendance. What tables would you create?
Students (StudentID, Name) and Attendance (Date, StudentID, Status).
Name two purposes of a database.
To store, organize, and retrieve data efficiently; to allow multiple users to access data simultaneously.
What is the difference between a field and a record?
A field is a single piece of data (column), while a record is a complete set of fields (row).
How do you add a new field to an existing table in Microsoft Access?
Open the table in Design View, click on a blank row, and enter the field name and data type.
How do you create a simple query to display all records from a table named Students?
Use the Query Wizard, select the Students table, and include all fields.
In a library database, how would you link the Books and Borrowers tables?
Use a foreign key (e.g., BorrowerID) in the Books table to link to the Borrowers table.
What is the difference between a flat file and a relational database?
A flat file stores data in a single table, while a relational database uses multiple related tables.
What is a foreign key, and how is it used?
A foreign key is a field in one table that links to the primary key in another table, establishing a relationship.
What is the difference between a one-to-one and a one-to-many relationship?
In a one-to-one relationship, one record in a table is linked to one record in another table. In a one-to-many relationship, one record is linked to multiple records.
How do you create a query to find all students in Grade 10 from the Students table?
Use the Query Design View, add the Students table, include the relevant fields, and set the criteria for GradeLevel to "10".
A company has a table named Employees with fields: EmployeeID, Name, Department, and Salary. How would you create a query to find the average salary in the Sales department?
Use the Query Design View, add the Employees table, include the Salary field, set the criteria for Department to "Sales", and set the Salary field to "Avg" in the Totals row.
What is a DBMS, and what is its role in a database system?
A Database Management System (DBMS) is software that manages databases, allowing users to create, modify, and query data.
Name three data types commonly used in databases.
Numeric, text, date/time, logical, currency.
How do you establish a primary key in a table in Microsoft Access?
Open the table in Design View, select the field(s), and click the "Primary Key" button.
How do you create a query to calculate the total sales from a table named Orders?
Use the Query Design View, add the Orders table, include the TotalAmount field, and set it to "Sum" in the Totals row.
In an online store database, how would you design tables for Products, Customers, and Orders?
Products (ProductID, Name, Price), Customers (CustomerID, Name, Email), Orders (OrderID, CustomerID, ProductID, OrderDate, TotalAmount).
What is the difference between a centralized and a distributed database?
A centralized database stores data in one location, while a distributed database stores data across multiple locations.
???????
Free Points
Design a table structure for a library database with at least four fields and specify their data types.
Example: Books (BookID: numeric, Title: text, Author: text, Genre: text, PublishedDate: date).
How do you create a query to find all orders with a TotalAmount greater than $100 and sort them by OrderDate?
Use the Query Design View, add the Orders table, include the relevant fields, set the criteria for TotalAmount to ">100", and sort by OrderDate in ascending or descending order.
A hospital wants to create a database to manage patients and appointments. Design the table structures and establish relationships between them.
Patients (PatientID, Name, DOB), Appointments (AppointmentID, PatientID, DoctorID, Date, Time). Use PatientID as a foreign key in the Appointments table.