Show Menu
Cheatography
  1. Home >
  2. Programming >

DBM Cheat Sheet (DRAFT) by

Revision cheatsheet for database module

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Entity­-Re­lat­ionship Model:

Word:
Defini­tion:
Example:
Repres­ented by:
Entity Set
a group of similar abstract objects.
In a movie database design, movies and stars are entities, and studios are another kind of entity.
Rectangles
 
It's like a class in object­­-o­r­i­ented progra­­mming but it only defines the structure of data, not operations on data.
They each form an entity set.
Attributes
These are properties of entities in an entity set
In a movie database design, attributes could be "­tit­le" and "­len­gth­" for movies
Ovals
 
Attributes are usually implem­­ented as relations, but not all relations come from entity sets
-
 
Attributes are of simple types, like strings or numbers.
-
Relati­onships
These are connec­tions between two or more entity sets, such as the "­Sta­rs-­in" relati­onship between the Movies and Stars entity sets.
  
Diamonds
 
A relati­­onship means that an entity in one set is connected to an entity in another set.
 
Binary relati­onships between two entity sets are most common, but the E/R model allows for any number of entity sets to be involved in a relati­onship.
Tuple
a row in a table in a database, repres­enting a unique instance of an entity or a combin­ation of entities
 
It contains values for each attribute of the entity.
Instances of an E/R Diagram:
describe database schemas, and while no actual data exists in the E/R model, it can be useful to visualize it as if it did.
 
Entities have values for each attribute, and relati­onships connect entities
 
The instance of a relati­onship is a set of tuples that are connected by the relati­onship.
 
These tuples are not the same as those in a relation, and their components are entities instead of primitive types.
 
Each row of the table repres­enting the relati­onship set is a list of connected entities from different entity sets.

Keys

Keys
an attribute or set of attributes which helps you to identify a row(tuple) in a relation (table)
 
They allow you to find the relation between two tables
Candidate Key
The minimal set of attributes that can uniquely identify a tuple(row) is known as a candidate key
 
The value of the Candidate Key is unique and non-null for every tuple
 
All are “prime attrib­utes.” Same as candidate key.
Primary Key
There can be more than one candidate key in relation out of which one can be chosen as the primary key
 
Exactly one
 
Every primary key is unique and non-null
 
Whichever is most flexible for us can be used as a primary key
 
Primary key(PK) is a subset of a Candidate key(CK)
 
There can be one or more CK, but exactly one PK
Alternate key
The candidate key other than the primary key is called an alternate key
 
Out of Employ­eeNum, Drivin­g_l­icense and Permit­Number, if Employ­eeNum is selected as Primary Key, then the Drivin­g_l­icense and Permit­Number automa­tically become the Alternate Keys
Super keys
The set of attributes that can uniquely identify a tuple(row) is known as a Super Key
 
Two keys together that create a unique attribute is a super key
 
Adding zero or more attributes to the candidate key generates the super key
 
You can say every candidate key is a super key, but vice versa is not true.
Foreign Key
Foreign keys are the column of the table which is used to point to the primary key of another table.

Weak/S­trong Entity Types

Weak Entity Types:
A Weak Entity Type is an entity type that does not have sufficient attributes to form a primary ke
The existence of a weak entity depends on the existence of an identi­fying or owner entity type.
The relati­onship between them is called an identi­fying (ID) relati­onship.
The identi­fying relati­onship type is always many-t­o-one from the weak entity type to the identi­fying entity type.
The weak entity type must have a discri­minator (one or more attrib­utes) for distin­gui­shing among its entities.
For example, in an employees database, Child entities exist only if their corres­ponding Parent employee entity exists.
Weak Entity Types in an ERD:
A weak entity type is identified by a double rectangle.
The discri­minator is underlined by a dashed line.
An identi­fying relati­onship is identified by a double diamond.
The fact that the existence of the weak entity requires the existence of an owner entity is captured by the total partic­ipation of the weak entity type in the relati­onship (double line).
The primary key of a weak entity type is the combin­ation of the primary key of its owner type and its discri­min­ator, e.g., (NI#, Cname) for Child.
Strong Entity
An entity with a Primary Key

Single & Mutlip­le-­table Queries

SELECT
desired attributes
FROM
one or more tables
WHERE
conditions on rows of the tables are satisfied
DELETE
Delete rows from a table based on a specific condition
 
e.g DELETE FROM table_name WHERE condition;
AVG
calculate the average of a numeric column in a table?
 
e.g.SELECT AVG(co­lum­n_name) FROM table_­name;
DISTINCT
retrieve unique values in a column or a set of columns.