Brainscape's Knowledge GenomeTM

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


See full index

z Cosc 304 (Databases) > Slides > Flashcards

Slides Flashcards

1
Q

Define:

database

A

A database is a collection of logically related data for a particular domain

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

Define:

database management system (DBMS)

A

A DBMS is a software designed for the creation and management of databases.

e.g. Oracle, MySQL

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

What is the difference between a database and DBMS?

A

A database is the data stored and a database system is the software that manages the data

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

What are the three schema/views in the ANSI/SPARC architecture?

A

Internal/physical, conceptual, external

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

Define:

Internal view

A

The physical representation of the database on the computer, that is, how the data is stored

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

Define:

Conceptual view

A

The logical structure of the database that describes what data is stored and its relationships.

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

Define:

External view

A

The user’s view of the database that provides the part of the database relevant to the user.

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

What are the benefits of the 3-schema architecture at the external level?

A
  1. Each user can access and view data independent of other users.
  2. Logical data independence, that is, conceptual schema changes do not affect external views
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What are the benefits of the 3-schema architecture at the conceptual level?

A
  1. A single, shared data representation for all applications and users.
  2. Users do not have to understand physical data representation details.
  3. Physical data independence: conceptual schema not affected by physical changes such as adding indexes or distributing data.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What are the benefits of the 3-schema architecture at the internal (physical) level?

A

Provides standard facilities for interacting with operating system for space allocation and file manipulation

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

Define:

relation

A

A relation is a table with columns and rows

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

Define:

attribute

A

An attribute is a named column of a relation

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

Define:

tuple

A

A tuple is a row of a relation.

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

Define:

domain

A

A domain is a set of allowable values for one or more attributes.

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

Define:

degree

A

The degree of a relations is the number of attributes (columns) it contains.

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

Define:

cardinality

A

The cardinality of a relation is the number of tuples it contains.

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

Define:

relational database

A

A relational database is a collection of normalised relations with distinct relation names.

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

Define:

intension

A

The intension of a relation is the structure of the relation including its domains.

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

Define:

extension

A

The extension of a relation is the set of tuples currently in the relation.

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

What are keys used for?

A

Keys are used to uniquely identify a tuple in a relation.

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

Define:

superkey

A

A superkey is a set of attributes that uniquely identifies a tuple in a relation.

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

Define:

key

A

A key is a minimal set of attributes that uniquely identifies a tuple in a relation.

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

Define:

candidate key

A

A candidate key is one of the possible keys of a relation.

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

Define:

primary key

A

A primary key is the candidate key designated as the distinguishing key of a relation.

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

Define:

foreign key

A

A foreign key is a set of attributes in one relation referring to the primary key of another relation.

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

True or false: A key is always a superkey.

A

True.

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

True or false: A superkey is always a key.

A

False, as superkeys do not need to have a minimal set of attributes, as long as they uniquely identify a tuple.

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

What are relational integrity rules for?

A

Integrity rules are used to ensure the data is accurate.

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

Define:

constraints

A

Constraints are rules or restrictions that apply to the database and limit the data values it may store.

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

Define:

domain constraint

A

In domain constraints, every value for an attribute must be an element of the attribute’s domain or be null.

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

Define:

entity integrity constraint

A

In a base relation, no attribute of a primary key can be null.

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

Define:

referential integrity constraint

A

If a foreign key exists in a relation, then the foreign key value must match a primary key value of a tuple in the referenced relation or be null.

33
Q

What constraint says that a primary key field cannot be null?

A

entity integrity constraint

34
Q

What constraint says that a foreign key must match a primary key or be null?

A

referential integrity constraint

35
Q

What constrains is violated if you try to put “aa” into a field meant for product pricing?

A

domain constraint

36
Q

In relational algebra, what does σ mean?

A

selection

37
Q

In relational algebra, what does π mean?

A

projection

38
Q

In relational algebra, what does x mean?

A

Cartesian product

39
Q

In relational algebra, what does ⋈ mean?

A

join

40
Q

In relational algebra, what does ∪ mean?

A

union

41
Q

In relational algebra, what does - mean?

A

difference

42
Q

In relational algebra, what does ∩ mean?

A

intersection

43
Q

Define:

equijoin

A

An equijoin only contains the equality operator (=) in formula F.

e.g. WorksOn ⋈ {WorksOn.pno=Proj.pno} Proj

44
Q

Define:

natural join

A

A natural join over two relations R and S denoted be R⋈S is the equijoin of R and S over a set of attributes common to both R and S.

45
Q

R is a relation with 10 rows and 5 columns. S is a relations with 8 rows and 3 columns. What is the degree and cardinality of RxS?

A 
degree = 8
cardinality = 80
46
Q

What does the selection operator do?

A

The selection operator is a unary operation that takes in a relation as input and returns a new relation as output that contains a subset of the tuples of the input relation.

47
Q

When are we guaranteed to never have duplicates when performing a projection operation?

A

When we use a superkey

48
Q

What does the union operator do?

A

Union is a binary operator that takes two relations, R and S, as input and returns an output relation that includes all tuples that are either in R or in S or in both. Duplicate tuples are eliminated.

49
Q

What do outer joins do?

A

Outer joins are used in cases where performing a join “loses” some tuples of the relation. If you wish to keep tuples from R that have no match in S (or vice versa), you use an outer join so that the tuple from R is included and padded with nulls for the attributes of S.

50
Q

Define:

query language

A

A query language is used to update and retrieve data that is stored in a data model

51
Q

Define:

relational algebra

A

Relational algebra is a set of relational operations for retrieving data.

52
Q

Define:

relation schema

A

A relation schema is a definition of a single relation. It’s the intension (structure) of the relation.

53
Q

Define:

relation instance

A

A relation instance is the extension (set of tuples) of the relation.

54
Q

What does a value of “null” represent in a relation instance?

A

A value of null represents a missing or unknown value.

55
Q

What does the Cartesian product return?

A

The Cartesian product takes two sets R and S and returns the set of all ordered pairs such that the first element is from R and the second element is from S.

56
Q

List the 7 properties of relations.

A
  1. Each relation name is unique.
  2. Each cell of a relation contains exactly one value.
  3. Each attribute of a relation has a distinct name.
  4. The values of an attribute are all from the same domain.
  5. Each tuple is distinct, i.e. there are no duplicates.
  6. The order of attributes is not really important.
  7. The order of tuples has no significance.
57
Q

Which relational algebra operators form the complete set?

A

σ, π, x, ∪, -

58
Q

In SQL, what command allows you to define your own types/domains?

A

CREATE DOMAIN

59
Q

Create a user-defined domain for Emp.title where the title is either EE, SA, PR, ME, or null, and the default title is EE.

A

CREATE DOMAIN titleType AS char(2)
DEFAULT ‘EE’
CHECK (VALUE IN (null, ‘EE’, ‘SA’, ‘PR’, ‘ME’));

60
Q

Where are SQL constraints specified?

A

They are specified in the CREATE and ALTER TABLE statements.

61
Q

What are the 5 types of SQL constraints?

A
  1. required data
  2. domain constraints
  3. tuple constraints
  4. entity integrity constraints
  5. referential integrity constraints
62
Q

What does the required data constraint do, and how can you enforce it?

A

This constraint specifies that a column must always have a data value (cannot be null). To do this, specify NOT NULL after the column definition.

e.g. 
eno CHAR(5) NOT NULL
63
Q

What does the domain constraint do and how can you enforce it?

A

This is used to verify that the value of a column is in a given domain, and we use CHECK to enforce it.

e.g. 
title CHAR(2) CHECK (title IN (null, 'mg', 'ee'))
64
Q

What does the tuple constraint do and how can you enforce it?

A

This is used to check an entire tuple instead of just one column. CHECK is still used, but it comes after you’ve defined your attributes/domains.

e.g.
CHECK ( (honors = ‘Y’ AND gpa > 3.5) OR honors = ‘N’)

65
Q

What does the entity integrity constraint do and how can you enforce it?

A

This says that the primary key of a table must contain a unique, non-null value for each row. The primary key is specified using the PRIMARY KEY clause.

e.g.
eno CHAR(5) PRIMARY KEY
66
Q

What does the referential integrity constraint do and how can you enforce it?

A

This defines a foreign key that references the primary key of another table.

e.g.
FOREIGN KEY (eno) REFERENCES Emp(eno)
67
Q

What is a schema?

A

A schema is a collection of database objects (tables, views, domains, etc) usually associated with a single user.

68
Q

What is the hierarchy of database objects that you can create, alter, and destroy?

A

Database –> Schema –> Table

69
Q

What can ALTER TABLE do?

A

It can add or drop columns, add or drop constraints, and add or drop default values for a column.

70
Q

What is DDL?

A

The database is described to the DBMS using Data Definition Language (DDL)

71
Q

What is DML?

A

Once a database has been created using DDL, the user accesses data using a Data Manipulation Language (DML)

72
Q

What’s the difference between DDL and DML?

A

DDL is for the creation of data structures within a database and DML is used for the manipulation of the data within a database.

73
Q

Define

program-data independence

A

This is when the application is not dependent on the data it is manipulating.

74
Q

What is the difference between data and schema?

A

A schema is a description of the structure of the database, whereas the data is the information that’s stored.

75
Q

Define:

integrity

A

Integrity rules are used to insure the data is accurate.

76
Q

Define:

constraints

A

Constraints are rules or restrictions that apply to the database and limit the data values it may store.

77
Q

What are the rules that an SQL identifier must follow?

A
  • May only have letters, digits, and underscore
  • be no longer than 128 characters
  • must start with a letter or underscore
  • cannot contain spaces
  • you can do anything you want in double quotes
78
Q

What does SQL stand for?

A

Structured Query Language

79
Q

What three relational algebra operations are combined in the SELECT statement of SQL?

A

selection, projection, and join

z Cosc 304 (Databases) (4 decks)

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