Database Design Vocab

Relation, Table, File

How to describe a data that's been organized through relationships (usually in form of a table)

Transitive Dependency

If column A depends on Column B and Column B depends on Column C. Then Column A is dependent on column C.

Index

In the context

Client - side

In the context of databases, the client acts as an access point to a database

composite index

Index based on two or more attributes Often used to prevent data duplication

Candidate Key

Is the least number of columns needed to force every row to be unique. "How many columns are needed?"

3NF (Third Normal Form)

Move non-key dependencies to another table. Then use a foreign key to reference them

Non-clustered index

Non-clustered indexes are not copies of the table but a sorting of the columns you specify that "point" back to the data pages in the clustered index. Add non-clustered indexes for queries that return smaller result sets

Composite Key

Two or more primary keys used in a table. Only one column needs to be a key

SQL (Structured Query Language)

Used to create and manipulate databases

Natural Key

When a unique identifier naturally occurs in a dataset for each entity (e.g. verified emails on a login website)

Partial Dependency

When attributes are only dependent on part of the primary key and not the entirety of it.

Null

When no value is inputed and left blank on a table (usually fill with 0 when debugging). aka empty value or no value.

Storing 1 to 1 relationships over multiple tables

When there is a complex attribute in a 1 to 1 relationship you can add a table to store data (usually in a nested manner) on said attribute. Creating a new table will improve database design as the "complex attribute" will invalidate the 1 to 1 relationship of the table

Atomic Value

When you break data into the smallest unit possible. Have each column and values be "one thing"

Database

Where we stay data in

foreign key constraints

enforces referential integrity by guaranteeing that changes cannot be made to data in the primary table if those changes invalidate the link to data in the foreign key table.

Record, Row, Tuple

in table going across

server-side scripting language

is a technique used in web development which involves employing scripts on a web server which produce a response customized for each user's (client's) request to the website

Cardinality

the maximum number of times an instance in one entity can be associated with instances in the related entity. Cardinality requires a child relationship as the minimum instances an entity can have is one

Modality

the minimum number of times an instance in one entity can be associated with an instance in the related entity

Clustered Index

Defines how SQL Server will sort the data stored inside the table and since that data can be sorted in only one way, it simply is not possible to have two clustered indexes on the same table.

Schema

Drawn out structure of database

One to One

Each entity in the relationship will have exactly one related entity. E.g. social security number. One and on relationships can be stored as an attribute (exclusive to the entity).

Many to Many

Entities on both sides of the relationship can have many related entities on the other side. In a University, a student can take several classes and classes can have multiple students.

Anomalies

Errors within data integrity.

Keys

-Unique -Consistent -Never Null -Protects integrity -Improves functionality and efficiency -Allows added complexity to database -Removes a lot of scenarios where duplicated data doesn't get stored

NOT NULL

A condition you can add to a column in which there can be no null values. (e.g. a common one would be not being able to add another entity to a table without an email).

Naming Convention

A consistency used to name databases, rows, columns, etc.

Primary Key

A field (or group of fields) that uniquely identifies a given entity in a table. Requirements include: Uniqueness, never changing, and never null

JOIN

A method of combining tables. A common context is to make a new table of a unique ID and the comments they post on a website.

DDL (Data Definition Language)

A part of SQL that is used to create and modify objects of a database such as tables, views, functions and stored procedures. Does not specifically change the values within the table itself

Table

A physical representation of a relation. USed to organized data

columns, attributes, fields

A predefined set of descriptions to describing certain attributes of entries in a table

Foreign Key

A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables. Foreign keys point back to the Primary key.

Referential Integrity

A set of rules that Access uses to ensure that the data between related tables is valid. "Keys" are constant between different tables

Data Manipulation Language (DML)

A specific language, provided with a DBMS, which allows users to access and modify the data, to make queries, and to generate reports. Insert values in database

Intermediary/Junction Table

A table used to connect different tables with conflicting keys. Think of it as a reconciliation table to clear up where foreign keys are pointed to.

Crow's Feet Notation

A type of entity relation diagram where the relationships are depicted using lines and 0s. These are more descriptive of relationships than the diagrams using simple arrows. Cardinality can be 1 or Many and the symbol is placed on the outside ends of the relationship line, closest to the entity, Modality can be 1 or 0 and the symbol is placed on the inside, next to the cardinality symbol. For a cardinality of 1 a straight line is drawn. For a cardinality of Many a foot with three toes is drawn. For a modality of 1 a straight line is drawn. For a modality of 0 a circle is drawn.

2NF (Second Normal Form)

All non-key columns must depend on the entire primary key. Remove Partial dependencies

Surrogate Key

An arbitrary key created for relational databases to identify a relationship between tables. The key itself has no other function beyond preserving the relationship (no world meaning). You have to synthesize new data so you have to add new data.

One to Many

An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity. In a forum, someone posting comments on several threads can take on a one to many relationship.

Record

Another name for Row

File

Another name for Tabel

Field

Another name for column

Relation

Another name for table or how how things can be connected.

Entry

Another way of saying "row"

Tuple

Another word for Row in a table. Usually used to described attributes.

Superkey

Any number of columns that forces every row to be unique. "Can every row be unique?"

Entity

Anything we store data about (users, transactions, comment's etc.)

Data

Anything we store in a database

Dependency

Attributes regarding a primary key in which the attribute is exclusively related to the primary key

Views

Based on the viewers relationship status to the database. The view shows a what the database allows them to see. (e.g. you can't see other peoples information considered private on social media).

Designing Many to Many Relationships

Break M:M into 1 to M and M to 1s. Use

Alternate keys

Candidate keys that are not selected to be primary key.

Database Management System (DBMS)

Controls database using code

1NF (First Normal Form)

Create a table for each related attribute, give each a primary key, ensure data is atomic. (No nested data, no duplicates entries, break down data into the smallest parts (hence atomic)). -Use foreign keys for duplicate data (e.g. 1 user with 2 registered emails) -Break cumbersome columns into more columns (e.g. split address to street, state, zip, country)

Keys

Creating uniqueness among values in the tables.

Integrity

Policies within database used to preserve how the database is intended to be used for.

Designing one to many relationship tables

Relationships redefined as the one is not exclusive and not bound to an x amount of relationships and the "many" is bound to exclusivity to one relationship is has. Use of Unique key of the "one" is the best way to store the many relationship to the one

Server - side

Server serves clients in some way based on the privilege's of the client

Relational Database Management System (RDMS)

Software that allows you cto control relational databases

Column

Specific Attribute

Rows

Specific Entry

Normalize/Normalization

Steps/Gameplan taken to get the optimal database design

Relational Dataase

Stores database in tables

Domain Integrity

The field values must be from a predefined domain. (e.g. Phone number column should only accept phone numbers that are "valid")

Value

The information put into a specific "cell"

Data Definition Language (DDL)

The language that allows a database administrator to define the database structure, schema, and subschema.

Parent Table

The main table of a relationship. When creating a relationship, this is the "one" side of the relationship and contains the primary key.

Database Design

The process of designing tables to protect integrity and eliminate anomolies

Database Normalization

The process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Essentially a checklist of checks to do on a relational database. There are 3 forms (1NF, 2NF, 3NF) and must be done in order.

Entity Integrity

The property of a relational table that guarantees each entity has a unique value in a primary key and that the key has no null values.

Child Table

The second table of a relationship. When creating a relationship, this is generally the "many" side of the relationship. One record from the parent table (such as clients) can be related to one or more records of the child table (such as sales). The child points back to the parent with the foreign key.

Attributes

Things we store about an entity

Back-end

What is going on behind the scene. Usually kept hidden from front end users for obvious reasons.

Front-end

What the end user sees when on a website or application

SQL Keywords

Words when writing code in SQL that have been predefined already (e.g. SELECT, JOIN, AND, etc.)

Primary Key

a field that uniquely identifies a record in a table

Compound Key

a primary key composed of more than one field that together necessarily provide a unique value. Usually in the form of a intermediary table swhere two forign keys are compounded together. A columns can be keys

Simple Key

a primary key that is a single data element

Secondary key

an identifier field or attribute that has some identifying information but typically does not identify the file with complete accuracy