Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Database > Database > Flashcards

Database Flashcards

1
Q

What is a Primary Key?

A

A primary key is a unique identifier for a record within a database table.
It ensures that each record in a table can be uniquely identified. Typically, a primary key is composed of one or more columns in the table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is a Foreign Key?

A

A foreign key is a column or a set of columns in one table that references the primary key columns in another table. It establishes a link between two tables and enforces
referential integrity, ensuring that relationships between tables remain consistent.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is a Composite Key?

A

A composite key is a key that consists of multiple columns, used together to uniquely identify records within a table.

It’s essentially a combination of two or more columns that together form a unique identifier.

Multipe Unique columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is a Unique Key?

A

A unique key is similar to a primary key in that it enforces uniqueness
constraint on a set of columns in a table. However, unlike a primary key, a table can have
multiple unique keys.

It can also be null

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is a Candidate Key?

A

A candidate key is a set of columns that can uniquely identify records within
a table.
From the candidate keys, one is chosen to be the primary key.

It can be a primary key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is CGI?

A

Common Gateway Interface
CGI is a standard protocol that defines how web servers communicate with external programs
or scripts. It enables web servers to generate dynamic web pages by executing programs or
scripts on the server and sending the output to the client’s web browser.
CGI allows web servers to interact with databases, process form data, generate dynamic
content, and perform other tasks beyond serving static web pages.

Perl, Tcl, C or C++

executes programs on the server to process data to make the site dynamic

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Whats the visual representation of CGI etc

A

Web browser to server
(HTTP Requests and Response)

Server to CGI Program
(CGI Gateway)

CGI Program to Database
(Query and Results)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is the purpose of CGI?

A

While it’s primarily associated with generating web pages dynamically based on user input, it can also be used to handle form submissions, including sending emails.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How can CGI be used for sending emails?

A
  1. Form Submission: You start with an HTML form on your webpage where users can input their email address, subject, message, etc.
  2. Action Attribute: In the <form> tag of your HTML, you specify a URL for the form’s action attribute. This URL points to a CGI script on your server.
  3. CGI Script: On the server, the CGI script receives the form data submitted by the user. It typically runs some code (often written in languages like Perl, Python, or PHP) to process this data.
  4. Email Handling: Within the CGI script, you write code to take the form data and use it to compose an email message. This usually involves using a library or built-in functions provided by the programming language to send an email via SMTP (Simple Mail
    Transfer Protocol).
  5. SMTP Server: The CGI script communicates with an SMTP server (either running locally or remotely) to send the email. It connects to the server, authenticates if necessary, and then submits the email for delivery.
  6. Feedback to User: After the email is sent, the CGI script might generate a response page to let the user know that their message has been successfully sent or inform them
    if there was an error.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What are some security considerations for CGI?

A

CGI scripts can introduce security vulnerabilities if not
implemented carefully. Common security risks include script injection, parameter manipulation,
and directory traversal attacks. Proper input validation, output encoding, and access control
measures are essential for securing CGI applications.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is a database?

A

A database is a collection of related data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is a DBMS?

A

A database management system is a system that enables users to create and maintain a database.

Oracle.
MySQL,
phpMyAdmin (MySQL,)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is a database schema?

A

A database schema defines how data is organized within a database.

It is how u organize it example: with tables PKs etc

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are the 5 Components of a Database System

A

Data
Hardware
Software
Users
Data Access Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What are the type of Users?

A

● Application Programmers: Develop software applications that interact with the
database.
● Online users: Access and manipulate data through interactive interfaces or web
applications.
● End users: Retrieve and analyze data to support decision-making and operational
activities.
● Database Administrators: Manage and maintain the database system, including tasks
such as performance tuning, security management, backup and recovery, and schema
evolution.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What are some examples of Database Access Languages?

A

SQL, My Access, Oracle, etc

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What are the 2 types of Database Languanges

A

Data Definition Language (DDL): Constructs the database schema. Example: CREATE
command.
CREATE, ALTER and DROP

Data Manipulation Language (DML): Accesses the database, providing statements for
retrieving, modifying, inserting, and deleting data. Example: INSERT command.

DDL is table manipulation and DML is data manipulation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What are Data Models?

A

Data models describe the structure of the database, including data types, relationships, and
constraints. They facilitate communication between users and database designers, helping to
understand the meaning of data and user requirements

u designing the database example using and ERD, Normalization

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What are the Characteristics of Data Models?

A

Usability (User Friendly)
Scalability (Dynamic Expansion)
Maintainability (Comments and Documentation)
Standardization (Consistent format and variable names)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is Usability?

A

User-friendly design ensures that the interface for submitting emails is intuitive and easy to understand.

This includes clear labels for form fields, helpful hints or instructions, and
appropriate error messages if something goes wrong.

Ease of modification allows for quick adjustments to the form layout or functionality without requiring extensive reworking of the underlying code.

This could involve separating the presentation layer (HTML/CSS) from the logic layer (CGI script), making it
easier to update one without affecting the other.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is Scalability?

A

Adaptable to growth means the system can handle an increasing number of users without significant degradation in performance.

This might involve optimizing the CGI script’s code for efficiency, implementing caching mechanisms, or
scaling resources such as server hardware or network bandwidth.

Performance considerations ensure that even as the system scales up, response times
remain acceptable for users. This might involve load testing the email sending
functionality to identify bottlenecks and optimizing the code or infrastructure accordingly.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is Maintainability?

A

Ease of maintenance is achieved through well-organized code with clear documentation
and comments. This makes it easier for developers to understand how the CGI script
works and make changes or fixes as needed.

Documentation should include explanations of the script’s purpose, how it interacts with
other components of the system, and any dependencies or requirements. Comments
within the code should clarify complex logic or algorithms, making it easier for future
developers to follow.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is Standardization?

A

Consistent naming conventions ensure that variables, functions, and other elements
within the CGI script have clear and meaningful names. This makes the code easier to
read and understand, reducing the likelihood of errors or confusion.

Standardized data types help maintain consistency and compatibility across different
parts of the system. For example, using a consistent format for dates and times ensures
that data passed between the CGI script and other components (such as databases or
external APIs) is interpreted correctly.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is Horizontal Scaling?

A

Horizontal scaling involves adding more machines or computers to a system to handle increased
load.

(Add More Computers)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What are the Pros and Cons of Horizontal Scaling?

A

Pros:
Increased fault tolerance: If one server fails, the others can continue to handle requests.

Better resource utilization: Can distribute load across multiple servers, maximizing resource usage.

Easily accommodates growing demand: Adding more servers can handle increased traffic or workload.

Cost-effective for certain workloads: Can use commodity hardware and add more nodes as needed.

Cons:
Complexity in managing distributed systems: Requires coordination and synchronization among multiple servers.

Potential for network bottlenecks: Increased communication overhead between nodes.

Data consistency challenges: Synchronizing data across multiple nodes can be complex.

Limited scalability in some cases: Certain applications may not be easily parallelizable.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What are some examples of Horizontal scaling?

A

Web Servers: Adding more web server instances to handle increased web traffic. For example, a website experiencing higher visitor numbers can deploy additional instances of its web server to distribute the load.
Database Sharding: Splitting a database into smaller, more manageable parts and distributing these parts across multiple servers. Each shard handles a subset of the data, allowing for better performance and scalability.
Content Delivery Networks (CDNs): Distributing content across multiple servers located in different geographical regions. This allows for faster delivery of content to users by serving data from the server closest to them.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What is Vertical Scaling?

A

Vertical scaling involves increasing the resources of individual machines to handle increased
load. This typically involves upgrading hardware components such as CPU, memory, or storage
to improve the performance and capacity of a single machine.

(Add More Power to current computers)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What are the Pros and Cons of Vertical Scaling?

A

Pros:
Simplicity: Adding resources to a single server is straightforward.

Performance benefits: Can leverage more powerful hardware for increased performance.

Easier to maintain: Fewer systems to manage and monitor.

Lower networking overhead: Communication within a single server is faster.

Cons:
Limited scalability: Eventually, the capacity of a single server may become a bottleneck.

Higher cost: Upgrading hardware can be expensive, especially for high-end servers.

Increased risk of downtime: Upgrading hardware may require system downtime.

Single point of failure: If the server fails, all services hosted on it are affected.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What are some examples of Vertical scaling?

A

Enterprise Resource Planning (ERP) Systems: ERP systems often require significant processing power and memory to handle complex business operations such as inventory management, order processing, and financial transactions. Vertical scaling by upgrading the server hardware allows these systems to accommodate growing data volumes and user loads.
Database Servers: In scenarios where a single, centralized database is critical for an organization’s operations, vertical scaling can be used to increase the capacity and performance of the database server. This ensures that the database can handle growing data volumes and concurrent user requests without compromising performance.
High-Performance Computing (HPC) Workloads: Applications such as scientific simulations, financial modeling, and weather forecasting require massive computational power to process large datasets and perform complex calculations. Vertical scaling by upgrading CPU, GPU, and memory resources allows these applications to execute computations faster and more efficiently.

30
Q

What are Joins in SQL?

What does it do?

A

Combines rows from two or more tables based on a related column.
Example Foreign key

31
Q

What are the types of Joins?

A

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN

32
Q

What does an INNER JOIN do?

A

Returns only the rows that have matching values based on the specified join condition

33
Q

What is the syntax of an INNER JOIN?

A

SELECT columns
FROM table1 INNER JOIN table2
ON table1.table1PK = table2.table1PK

Example
SELECT orders.order_id, customers.customer_name
FROM orders INNER JOIN customers
ON orders.customer_id customers.customer_id;]
Result:

Retrieves order IDs and customer names where there is a matching customer ID in both
orders and customers tables.

34
Q

What does a LEFT JOIN do?

A

Returns all rows from the left table (table1) and but only the matched rows from the right table (table2). If there is no match, NULL values are returned from the right table.

35
Q

What is the syntax of an LEFT JOIN?

A

SELECT columns
FROM table1 LEFT JOIN table2
ON table1.table1PK = table2.table1PK

36
Q

What does a FULL OUTER JOIN do?

A

Returns all rows when there is a match in either table.

If there is no match, NULL values are returned for the
missing side.

(Retrieves all customer names and order IDs. If a customer has no orders or an order
has no corresponding customer, NULL values are returned accordingly.)

(Puts everything and puits NULL if theres no value.)

37
Q

What is the syntax of a FULL OUTER JOIN?

A

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.colum

(Retrieves all customer names and order IDs. If a customer has no orders or an order
has no corresponding customer, NULL values are returned accordingly.)

38
Q

What are the 4 types of SQL Clauses?

A

GROUP BY
ORDER BY
WHERE
LIKE

39
Q

What does GROUP BY do?

A

Groups rows that have the same values into summary rows.

40
Q

What is GROUP BY syntax?

A

SELECT column1, COUNT(column2) FROM table_name
GROUP BY column1

41
Q

What is ORDER BY?

A

Sorts the result set in ascending or descending order

42
Q

What is the ORDER BY syntax?

A

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC/DESC

43
Q

What is WHERE?

A

Filters records based on specified conditions

if statement

44
Q

What is WHERE syntax?

A

SELECT * FROM table_name
WHERE condition

returns what meets the condition

45
Q

What is LIKE?

A

Used in a WHERE clause to search for a specified pattern in a column

46
Q

What is the LIKE syntax?

A

SELECT * FROM table_name
WHERE column1 LIKE ‘pattern’;

% and _
a% (must contain “a”)
f_re (looks for sum tht in the blank) fire

47
Q

What is a Database Architecture?

A

A Database Architecture represents the Database Management System’s (DBMS) design
(schema). The DBMS architecture makes it easy to understand the components involved in the
database system and their relations. The DBMS architecture may vary based on the system on
which it runs. Some systems contain both client and server machines whereas some run only on
a server machine. Based on the architecture, the system can be classified as centralized,
decentralized, or hierarchical.

48
Q

What are the 3 Layers / Tiers present in system architecture of an application

A

Presentation Layer
Application Layer
Data Layer

49
Q

What is the Presentation Layer?

A

The presentation layer is often referred to as the client and is the topmost layer in an
application. The end-user has access to the application layer. This layer passes all the
information that is given by the end-user. In simple terms, it is the application that we use. For
instance, the login page of Facebook.

Client side

50
Q

What is the Application Layer?

A

AKA (Business layer)
The application layer is also known as the business layer. It interacts with the end-user and
sends the necessary details to the presentation layer. This layer acts as a mediator between the
presentation and database layers. For instance, when the login button is clicked on the
Facebook registration page, it passes the details to the presentation layer.

Server Side

51
Q

What is the Data Layer

A

The data layer is the server. It communicates with the database to share and retrieve data.
This layer contains the functions that connect with the database and performs actions such as
insert, update, delete, search, and so on.

Database/ Server

52
Q

Centralized vs Decentralized?

A

The database can be stored at one location (centralized) or multiple locations (decentralized).

53
Q

What are 3 Types of database architecture? No.s

A

One-Tier Architecture
Two-Tier Architecture
Three-Tier Architecture

54
Q

What is the One-tier architecture?

A

AKA Local database system

One -Tier (Single-Tier) Architecture is the simplest DBMS architecture. In this type of
architecture, the client, server, and database are available on the same machine. No internet
connection is required to perform any action on the database. All the changes are reflected in
the database directly. Additionally, single-tier architecture is also referred to as a local database
system.

Client, server, and database are available on the same machine

55
Q

What is the Two-tier architecture?

A

A Two-Tier Architecture operates as a two-way system. The database system is stored on the
server in this client-server architecture, and the DBMS application is on the client-side. Both of
these machines are connected through a network. However, the client and server machines
cannot communicate directly. To access the database on the server, the client has to make a
request using a query language, that is, SQL. When the server receives the query, it acts and
returns the result to the client. The communication between the application program and the
database server is achieved using APIs such as ODBC, JDBC.

Client-Server (Database)

56
Q

What is the Three-tier architecture?

A

A Three-Tier Architecture is the widest used architecture. In this type of Architecture, an
additional layer, known as the Application Layer, sits between the client (presentation layer) and
the server (database) machine. This application layer offers a Graphical User Interface (GUI),
making it easy for the end-user to interact with the database. In brief, the application layer
handles the communication between the client and server.

Client-Application-Server (Database)

57
Q

What is CRUD?

A

CRUD stands for Create, Read, Update, and Delete. These are the basic operations performed
on data in a database.

58
Q

What is Create?

A

Create (INSERT):
Used to add new records (rows) to a table.

59
Q

What is the Create Syntax?

A

INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

60
Q

What is Read?

A

Read (SELECT):
Used to retrieve data from a database.

61
Q

What is Read Syntax?

A

SELECT column1, column2,
FROM table_name WHERE condition

* For every column

62
Q

What is Update?

A

Update (UPDATE):
Used to modify existing records (rows) in a table.

63
Q

What is the Update Syntax?

A

UPDATE table_name SET column1 = value1, column2 = value2
WHERE condition;

64
Q

What is Delete?

A

Delete (DELETE):
Used to remove records (rows) from a table.

65
Q

What is the Delete Syntax?

A

DELETE FROM table_name WHERE condition;

66
Q

What is an Entity Relationship Diagrams?

A

Visual representations of the relationships between entities in a database.

67
Q

What are the components of an ERD?

A

**Entities (Rectangles): **
Represented by rectangles, such as “Customer” or “Product.”

**Attributes (Ovals / Ellipses): **
Properties of entities, shown within ovals.

**Relationships: **
Connections between entities, indicated by lines.

Cardinality Notations:
Describes the numerical relationship between associated entities

68
Q

What is Data Redundancy?

A

Imagine you have a list of your favorite books, and you keep this list in two places: on your phone and on your computer. If you update the list on your phone but forget to update it on your computer, you now have two slightly different versions of the same information. This duplication is what we call data redundancy - having the same data stored in multiple places. It can lead to confusion and inconsistency if not managed properly.

69
Q

What are some causes of Data Redundancy

A

No normalization

Manual Data Entry: Human error during data entry can result in duplicate or redundant records.

Data Replication: Replicating data across multiple systems for backup or performance reasons can inadvertently lead to redundancy if not synchronized properly.

Multiple data entry points: When data can be entered or updated from various sources or interfaces, it increases the chances of redundancy.

No updates: Inconsistent data updating or lack of data validation can result in duplicate entries.

Old Data: Keeping outdated or obsolete data without proper archival can contribute to redundancy.

70
Q

What is the Impact of Data Redundancy?

A

Increased storage requirements: Storing duplicate data consumes additional disk space. and cost.

Data inconsistency: When redundant data is updated in one location but not in others, it can lead to inconsistencies and errors.

Decreased data integrity: Redundant data increases the risk of data anomalies such as insertion, deletion, and update anomalies.

Difficulty in data maintenance: Managing redundant data complicates data maintenance tasks such as updates and backups.

Database (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions