Shared Flashcard Set

Details

Comp2004 - Flashcard Set 1 - Overview
Comp2004 - Flashcard Set 1 - Alejandro Saucedo
20
Computer Science
Undergraduate 2
05/20/2013

Additional Computer Science Flashcards

 


 

Cards

Term
What is a relation?
Definition
  • A subset of the cartesian product Ax ... x An
  • In practice it is represented as a two dimensional table. 
  • Columns are called attributes and rows are called tuples
  • Each row represents an n-tuple of R
  • Ordering of rows is immaterial
  • All rows are distinct
  • Ordering of attributes is significannt

 

Term
What is a schema and give an example
Definition

Student(id:integer, lastName:string)

 

Specification of each attribute and the order in which they appear.

 

It is static

 

A collection of these gives us the relational database schema

Term
What is relational extension
Definition
The state of the relation at any given instance; that is a set of tuples {t1, ... , tm}
Term

Transform into normal form

[image]

Definition
[image]
Term
Primary keys
Definition
To identify the super keys we consider the powerset of the relation - The ID chosen must be unique.
Term
Foreign Keys
Definition

A set of attributes FK in a relation R is a foreign key of R that references the relation S if it satisfies:

  • The attributes in FK have the same domain(s) as the primary key of S
  • In the current state of R, the value of the FK exists as the value of the primary key in S or is NULL
Term
Show the query to create student table
Definition

drop table if exists Student;

create table Student

(

ID integer PRIMARY KEY,

email varchar(20) UNIQUE NOT NULL,

lastName varchar (20),

firstName varchar(20),

DOB date

);

 

Term
Create a Course table and a Transcript table with foreign keys referencing to student
Definition

drop table if exists Course;

create table Course;

(

Code char(8) PRIMARY KEY,

title VARCHAR(50

);

drop table if exists Transcript;

create table Transcript

(

Student_Id Integer,

Course_Code char(8),

mark integer default 0,

PRIMARY KEY(Student_ID, Course_Code),

FOREIGN KEY (Student_ID) REFERENCES Student(Id),

FOREIGN KEY(Course_Code) REFERENCES Course(Code)

);

Term
Database design process
Definition
  • Conceptual - Ideas; This is for client and stakeholders and is independent of the database that will eventually emerge.
  • Logical - High Level Design; We identify entities or objects in the system, the attributes of properties of the entities we need to store and the relationship between objects.
  • Physical - Low Level; We have chosen a DBMS and now map the items into the physical tables. Performance optimisation may be needed here.

The resulting diagram is called an entity relation (ER) diagram

Term
Derived attribute
Definition
Not stored in database but computed on demand
Term
Strong/Weak entity
Definition

Strong entities are in sense independant and can exists indepentendtly.

 

Weak entities can only exist when there is a relationship with a strong entity.

 

Pet would be a weak entity which is defined an identifying relationship with its owner

Term
One to many relatioship
Definition
A parent record in a table can reference several child records.
Term
Many-to-many
Definition
Relationships with a bidirectional one-to-many relatiosnhip
Term
Recursive relationship
Definition
Attributes that refer to another instance of itself. (Parent relationship)
Term
σ operation
Definition

Select σ operation acts on a single relation and acts as a filter keeping only th tuples that satisfy a qualifying relation. (WHERE)

 

e.g. σmark>70

σCourse_Code=Comp1008 Λ mark > 50

Term

Show variations for

σCourse_code = COMP1008 Λ mark > 50(Transcript)

Definition

σCourse_code = COMP1008(σCOMP1008 Λ mark > 50(Transcript))

σCOMP1008 Λ mark > 50(σCourse_code = COMP1008(Transcript)

Term
∏ Operator
Definition

 projection oeprator constructs a new relation form selected columsn of R (SELECT)

 

LastName, FirstName(Student)

Term
Cartesian product
Definition
All possible combinations in the cartesian product
Term
Theta join |x|
Definition

The theta join, joins a table in the value specified

 

Student |x|id=Student_id Transcript

Term
Constraints for set operations (union, etc) on db relations
Definition
  • Relations R and S have schema with identical sets of attributes
  • The order of attributes in each relation is identical
Supporting users have an ad free experience!