Test #2 Review

Question 1

Entity Type / set

Answer 1

denoted as a rectangle

Question 2

Relationship

Answer 2

Diamond that connects two entity types

Question 3

Attribute

Answer 3

describes a fact about an entity of interest
ex. professors birth date

Question 4

Composite attribute

Answer 4

a value that has multiple parts, such as professors name which may have first, middle, last, etc.

Question 5

Derived attribute

Answer 5

a value that may be derived from some others attribute in the entity.
ex. age which can be derived from birthdate

Question 6

Multi-valued attribute

Answer 6

An attribute named contact numbers may contain several phone numbers at which a professor may be accessed

Question 7

Generally speaking, each ______ will become a table in the database, with its ______ as columns

Answer 7

entity type, attributes

Question 8

For relationship types, we need to know about their ____________

Answer 8

cardinality constraints

Question 9

Cardinality constraints

Answer 9

specify how many instances of related entity types can be associated with each other.

Question 10

Name the ways in which two entities can be associated:

Answer 10

one to one
one to many/many to one
many to many

Question 11

Key

Answer 11

unique identifier that can be used to distinguish instances in an empty set

Question 12

True/False | There may be multiple keys due to the nature of business data

Answer 12

TRUE

Question 13

Candidate key

Answer 13

all possible candidate attributes for the primary key

Question 14

Surrogate key

Answer 14

An artificially generated identifier, typically an auto-incrementing integer, used to uniquely identify a record.

Question 15

Natural key

Answer 15

unique identifier derived from the data itself

Question 16

How is a composite attribute represented

Answer 16

Normal circle

Question 17

How is a Derived attribute represented

Answer 17

dashed circle

Question 18

How is a multi-valued attribute represented

Answer 18

double circle

Question 19

Types of constraints:

Answer 19

PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL

Question 20

How to create a table in SQL

Answer 20

CREATE TABLE TableName (
data datatype PRIMARY KEY
data datatype UNIQUE NOT NULL
);

Question 21

Foreign key

Answer 21

references a key in another table

Question 22

how to specify foreign key

Answer 22

FOREIGN KEY REFERENCES tableName (keyName)

Question 23

Data Manipulation Language

Answer 23

INSERT
UPDATE
DELETE

Question 24

How to insert into table in SQL

Answer 24

INSERT INTO TableName
VALUES ( ‘title’ , number );

Question 25

CHECK

Answer 25

Constraint that allows specificity within data ex. Seats int CHECK (Seats > 0) = only pos seats

Question 26

How to use UPDATE

Answer 26

UPDATE TableName SET AttributeName = newValue (optional) WHERE AttributeName = specificValue

Question 27

How to use DELETE

Answer 27

DELETE TableName WHERE AttributeName = specificValue

Question 28

GETDATE()

Answer 28

Returns datetime value ex. DAY ( GETDATE () ) AS Day

Question 29

DAY ( date ) , MONTH ( date ) , Year ( date )

Answer 29

Returns integer that represents that day, month, year of specified date

Question 30

DATEDIFF

Answer 30

( datepart , startdate , enddate )

Question 31

IIF

Answer 31

IIF ( boolean , true value , false value )

Question 32

LEFT, RIGHT

Answer 32

LEFT ( exp , n) RIGHT ( exp , n ) returns part of character string as specified (n spots from left or right).

Question 33

SUBSTRING

Answer 33

SUBSTRING (expr , start , length )

Question 34

LOWER UPPER

Answer 34

LOWER (str expr) UPPER (str expr) Returns a character expression with letters in the specific case

Question 35

LEN

Answer 35

LEN (str expr)

Question 36

weak entity set

Answer 36

related to a strong entity set, represented by double outline

Question 37

weak entity set never has

Answer 37

primary key

Question 38

composite attributes should be represented as _____ in relational model

Answer 38

individual attributes

Question 39

combining strong and weak entity set into table

Answer 39

take PK from strong and combine with all weak attributes

Question 40

combining many to many relationships

Answer 40

take PK from both entities to form table

Question 41

How to handle Multivalue attributes

Answer 41

create new table and store as FK in old ex. Locations -> location and locationID (FK)

Question 42

To handle many to many relationship

Answer 42

create table of relationship and store PK of both as well as any attributes from relationship

Question 43

To handle 1 to many relationship

Answer 43

Put the PK from 1 into many table`