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