Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

DBMS > Exam 1 > Flashcards

Exam 1 Flashcards

(78 cards)

Start Studying
1
Q

Database

A

Interrelated collection of data

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

Why databases

A

Easy for software engineers to manage datasets.

They are efficient and robust

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

DBMS

A

Application that facilities inserting, deleting, updating, and querying data within a database.

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

Why DBMS

A

Users don’t need to know how data is physically stored. They only need to see simple views of the data.

Keeps data in a good state

Efficient

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

DML

A

Data manipulation language

Either declarative or procedural

SQL = declarative
relational algebra = procedural

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

DDL

A

Data Definition Language

provides the ability to define the structure (schema) of data

Identify data types, provide data constraints, specify referential integrity, make assertions, define auth levels

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

SQL

A

Structured Query Language

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

select

A

filters tuples from a relation

σ (predicate) Relation

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

project

A

filters columns from a relation

π (attr1, attr2, …) Relation

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

join

A

Creates pairs from like attributes

Relation ⨝ (attr) Relation

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

Union

A

found in either

relation U relation

query1 union query2

add ALL to keep all duplicates

query1 UNION ALL query2

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

Intersection

A

Found in both

Relation ∩ Relation

query1 intersect query2

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

Set Difference

A

provides set based filtering

A - B =

set that contains those elements of A that are NOT in B

query1 except query2

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

And

A

this sign ^

AND in sql

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

Or

A

V

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

schema

A

defines how records can be described by identifying attributes and their primary keys

ex) Course_offering(ID, name, credits)

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

Relation Instance

A

Specific instance of such a table

A singular view of it

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

Attribute Domain

A

Describes the possible range of values an attribute can take

ex) student year {fr, so, jr, sr}

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

Super Key

A

Set of attributes that uniquely identify tuples within a relation.

No 2 tuples in the relation may have the same value for the key

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

Primary Key

A

Key chosen by the DB designer as the principal means of uniquely identifying tuples within a relation.

No 2 tuples in the relation may have the same value for the key

Can’t have 2 students whose ids are the same

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

Foreign Key

A

A relation may include the primary key of another relation. Foreign keys are used to reference the other relation

May have 0, 1, or Many

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

Functional Dependencies - what

A

An understanding that you can determine the values for one set of attributes from another

ex) an employees SSN will allow us to determine their salary

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

Functional Dependencies - why

A

Whenever a change is made, we must check all the functional dependencies that exist to determine if we should force the modification to fail.

Must preserve the functional dependencies at all cost!

TLDR
-Data integrity (avoid anomalies)
-Normalization (condensing tables into smaller, more efficient tables)
-efficient database design (avoid redundancy)

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

Query Types

A

Select, Insert, Delete, Update

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
select
Basic Select Query Structure SELECT ___ FROM ___ WHERE ___
26
Insert
Insert into tableName (attr1, attr2, attr3) values ("value1", "value2", value3);
27
Delete
DELETE FROM tableName WHERE attr1 = value1 or attr2 > value2; DELETE FROM tableName; will delete all rows in tableName
28
Update
UPDATE tableName SET attr1=value1 WHERE attr2>value2 without a where clause all records are updated UPDATE employees SET salary=salary*1.04 give every employee a 4% raise
29
where
Identify any desired restrictions
30
group by & having
group by allows you to preform aggregation on groups of tuples that have similar values ex) select dept_name, count(*) as numStudents from student group by dept_name having numStudents > 2; ^ predicates cam be applied to the results of group by using HAVING
31
aggregation
avg() min() max() sum() count()
32
order by
ASC or DESC ex) select name, tot_cred FROM student where dept_name = "Comp. Sci." order by tot_cred ASC;
33
Limit
add LIMIT (int) at the end of a query to limit the number of rows returned
34
From
Identify the tables the data comes from
35
SQL Injection - Risk?
bad things happen to good people "bad" or malicious input is executed as SQL leads to unauthorized access or manipulation of data/database ex) select userID from users where username = " " and password = " "; username = "admin" password = ???" or "1"="1
36
SQL Injection - Fix?
Input sanitization prepared statements
37
Pattern Matching
% matches any substring "%science" matches "computer science" _ matches any character "Comp. _ _ _." matches "Comp. Sci." and "Comp. Eng." Must use LIKE operator select * from course where title LIKE "Intro%";
38
Joins
FROM Relation1, Relation2, Relation... ^ without specifying a predicate or columns to join/match on the result is a cross product FROM instructor join teaches on instructor.id = teaches.id FROM instructor NATURAL JOIN teaches
39
Entities
A thing or object that is distinguishable from other things or objects Described by a set of attributes
40
Relationships
An association among several entities Degrees of relationships: Binary and Tertiary Tertiary exist but are more rare
41
Cardinality of Relationships
one to one one to many many to one many to many
42
participation constraints
Either total or Partial
43
Total participation
Entity must participate in the relationship ex) Students must be advised by an instructor double lines
44
Partial participation
Entity may participate in the relationship ex) Instructors may advise students single line
45
attribute types
simple and composite single valued & multivalued derived attributes can be determined from other attributes
46
What is the eviction policy for most buffer managers/pools?
Least Commonly Used Evict the page in the buffer pool that was used the longest ago
47
What is a buffer manager? (Chapter 13)
A buffer manager implements the logic that determines which blocks should be added or evicted from the buffer pool.
48
What is a buffer? (Chapter 13)
A space we allocate in main memory to keep copies of often fetched blooks.
49
What are the three parts of a slotted page structure? (Chapter 13) (Variable length record)
Block headers, free space, and records.
50
What is Variable Length Record? (Chapter 13)
A length record where offsets are stored at the beginning of a record, and values at the end. An optional null bitmap can be used to represent null values.
51
What is Fixed Length Record? (Chapter 13)
All records take up the same space on storage. We know how many fit in a block, gives us random access, and faster lookups.
52
What are blocks and pages? (Chapter 13)
Data storage structures data read/written from/to disk are stored in blocks data read/written from/to RAM are in the form of pages Block size is specified by the file system page size is specified by the OS both typically around 4KB
53
What is RAID 1? (Chapter 12)
Mirroring Data is copied onto 2 disks, making it so if one of the disks fails, you can still access your data. for data redundancy cuts your amount of storage in half
54
What is RAID 0? (Chapter 12)
striping Data is split up into two disks, alternating which disk information is put in. for better performance still get access to your total amount of storage
55
What does RAID do? (Chapter 12)
Splits up data/files into different disks, usually 2.
56
What does RAID stand for? (Chapter 12)
Redundant Array of Inexpensive Disks.
57
In determining access time for magnetic disks, how long does rotational latency take on average? (Chapter 12)
1/2 the worst-case rotational latency. Usually 4-11 milliseconds.
58
In determining access time for magnetic disks, how long does seek time take on average? (Chapter 12)
1/2 the worst-case seek time. Usually 5-10 milliseconds.
59
In determining access time for magnetic disks, what happens before anything else? (Chapter 12)
The spin up access time = seek time + rotational latency
60
What are sectors in magnetic disks? (Chapter 12)
The smallest unit of data that can be read or written. Size is usually 512 bytes.
61
How many circular tracks are platters divided into? (Chapter 12)
50k - 100k
62
How do magnetic disks read/write data? (Chapter 12)
Using a read/write head that sits very close to the platter reads/writes magnetically encoded information. Only one head per platter that is mounted on a common arm
63
What is the storage hierarchy, from the top down? (Chapter 12)
Cache, main memory, flash memory, magnetic disk, optical disks, magnetic tapes. Fastest/Most expensive at top Slowest/Cheap at the bottom
64
What are the 4 classifications of physical data storage? (Chapter 12)
Speed, cost per byte, reliability, and volatility
65
What does DDL look like?
CREATE TABLE student ( name varchar(100), id numeric(6,0), dept_name varchar(30), tot_cred numeric(3,0). primary key (tot_cred), foreign key (dept_name) references department(department_name) );
66
What should you produce schema for based on an ER diagram? (Chapter 6)
Produce one based on each entity set strong relation (many-to-many) set, and weak entity/relation set. Attributes generally respond to columns in a relation model. Remember your primary keys as well! Strong entity sets reduces to a table whose columns correspond with the same attributes Many to many relationship sets are represented by a table with attributes for the primary keys of the participating entity sets
67
What does schema for the ER model look like? (Chapter 6)
Using the university database: Student(ID, name, dept_name, tot_cred) Pretend the ID is underlined.
68
What are the two main benefits of DBMSs? (Chapter 1)
Physical data independence and data abstraction.
69
What is an Entity-Relationship model? (Chapter 6)
A form of the relational model that utilizes geometrical representations of entities and relationships.
70
What are the three basic concepts in the ER data model? (Chapter 6)
Entity sets, relationship sets, and attributes.
71
What are entity sets represented as? (Chapter 6)
Rectangles that contain a list of attributes. Primary keys are underlined!
72
What are relationship sets represented as? (Chapter 6)
Diamonds with lines connecting two entity sets.
73
What is one-to-one cardinality represented as? (Chapter 6)
Two arrows on either side of the diamond.
74
What is one-to-many/many-to-one represented as? (Chapter 6)
One arrow and one undirected line.
75
What is many-to-many represented as? (Chapter 6)
Two undirected lines.
76
Free Lists
use the header of a block to refer to the next empty/deleted record space a empty/deleted space points to the next empty space
77
first option of variable length records
offsets are stored at the beginning of the record values come last 0000 null bitmap can be used to represent null values for any attribute
78
Data transfer rate for mangnetic disks
25 - 100MB Lower for inner tracks

DBMS (2 decks)

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