Professor Materials Week 3

Question 1

What are the elements of ER Models?

Answer 1

Entity
Attribute
Relationship

Question 2

What is an entity class?

Answer 2

Something the user wants to track.
Typically nouns.
Represented by a rectangle with a singular name

Question 3

What is an entity instance?

Answer 3

The actual occurrence of the data
Usually never shown

Question 4

What is an attribute?

Answer 4

data item that is used to describe an entity.
show by ovals and connected to the entity rectangle with a line

Question 5

Simple vs Composite attributes

Answer 5

Simple – composed of one piece of data
composite – an attribute composed of other attributes (like an address)
Not shown in crow’s foot notation

Question 6

Single vs mutli-valued attr

Answer 6

Single – one single piece of value
Multi (double circle) – an attribute that stores multiple data values (skills = carpentry, plumbing, painting)

Question 7

Store vs derived attr

Answer 7

Stored – actually stored
Derived – the data value is calculated (dotted circle) age (calc by DOB)

Question 8

What is identifier?

Answer 8

Most entities will have one
One or more attributes that identify an entity instance
If two or more, composite identifier

Can be unique or non-unique

Denoted by underline in Chen Notation

Question 9

What are the steps to transposing data mdoel into relations?

Answer 9

Transposing:
1. Name of hte entity becmes the name of the relation
2. Attributes become the attributes of the relation
3. The identifier of the entity becomes the proposed primary key of the relation, denoted by underlining

Question 10

What is Relational Schema notation?

Answer 10

RELATION(attr(underlined), attr2, att3)
A(2,3(both 2 and 3 underlined), 4,5)

Question 11

What is 1NF?

Answer 11

First normal form.
* Rows contain data about tuples or instances of the relation
* Cells of the relation hold a single value (no arrays)
* Columns are attributes of the relation
* All entries in a column are of the same domain (physical and logical)
* EAch column has a unique name
* The order of the attr is unimportant
* The orer of the tuples is unimportant
* No two tuples may be identical (must have primary key can be either) – natural primary key vs surrogate primary key (used only to guarantee uniqueness)
* No repeating groups – more than one attr from the same physical and logical domain (ex author1, author1email, author2, author2email)

Question 12

How do you create a relational schema with DMBS?

Answer 12

Using SQL.

Question 13

Data Definition Language DDL

Answer 13

Primarily metadata, CREATE DROP

Question 14

Data Manipulation Language DML

Answer 14

User data, INSERT, SELECT

Question 15

Transaction Control Language TCL

Answer 15

Commit, rollback. Data Control Language

Question 16

Commands to create a database

Answer 16

CREATE DATABASE databaseName;

Question 17

Command to remove a databse

Answer 17

DROP DATABASE databaseName;
DROP DATABASE IF EXISTS databaseName;

Question 18

How do you open a database

Answer 18

USE databaseName;

Question 19

Attribute data types

Answer 19

CHAR(size) – fixed length
VARCHAR(size) – var length
Numeric – INT or INTEGER
Date - DATE

Question 20

What is a constraint?

Answer 20

a rule limiting the values allowed, primary key is an example

Question 21

How do you add to table?

Answer 21

INSERT INTO tableName
(fieldlist) VALUES (valuelist);