1/84
Vocabulary practice flashcards covering fundamental database concepts, data types, relational modeling, SQL syntax, normalization, administration, indexing, and security for the Microsoft Technology Associate (MTA 98-364) exam.
Database (Bază de date)
A structured collection of data stored according to specific rules that govern access, management, and updating of the information.
Database Management System (DBMS)
Software that manages and stores data in a database, allowing the creation, modification, and querying of data, as well as transaction management, security, backup, restoration, and data integrity.
SQL Server
A relational database management system (RDBMS) developed by Microsoft.
SQL Server Management Studio (SSMS)
A graphical user interface developed by Microsoft that allows users to connect to SQL Server, write and execute SQL queries, inspect table structures, monitor server activity, and manage permissions without using a command line.
Relational Model (Modelul relațional)
A database model where data is stored in tables linked by primary and foreign keys, enabling complex queries via SQL (e.g., MySQL, Oracle, SQL Server).
Hierarchical Model (Modelul ierarhic)
A database model that organizes data into a tree-like structure where each record (node) has exactly one parent but can have multiple children (e.g., IBM file management system 1966-1968).
Network Model (Modelul de rețea)
A database model similar to the hierarchical model, but allowing each record to have multiple parents, creating a complex network of relationships.
NoSQL Database Model
A non-relational database model designed to store, access, and manage unstructured or semi-structured data across various formats, such as document stores, key-value stores, column stores, or graph databases.
Flat File Model
A rudimentary database model where data is stored in plain text files (e.g., .csv, .txt) organized in rows and columns delimited by commas, tabs, or other characters, primarily used for simple storage and fast sequential access.
Table (Tabel)
A two-dimensional structure in the relational model that organizes data into rows (records) representing unique entity instances and columns (attributes) containing specific properties.
Column (Coloană / Attribute)
A vertical component of a database table characterized by a unique column name and a specific data type that defines what category of information it contains.
Row (Rând / Record)
A horizontal component of a table that holds the complete set of data for a single unique entry or entity instance.
Field (Câmp)
The intersection of a row and a column that holds a single, specific attribute value for a given record.
bit
An exact integer data type that can take a value of 0, 1, or NULL, using 1 byte of storage.
tinyint
An exact integer data type that stores whole numbers from 0 to 255, using 1 byte of storage.
smallint
An exact integer data type that stores whole numbers from -32,768 to 32,767, using 2 bytes of storage.
int
An exact integer data type that stores whole numbers from -2,147,483,648 to 2,147,483,647, using 4 bytes of storage.
bigint
An exact integer data type that stores whole numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, using 8 bytes of storage.
decimal(p,s) / numeric(p,s)
Exact numeric data types where p specifies precision (total number of digits) and s specifies scale (digits to the right of the decimal point), storing numbers from −1038+1 to 1038−1 using 5-17 bytes.
char(n)
A fixed-length non-Unicode character string data type storing 0 to 8000 characters; strings shorter than the specified length are padded with trailing spaces (' ').
varchar(n)
A variable-length non-Unicode character string data type storing 0 to 8000 characters; it allocates storage only for the actual characters entered plus 2 bytes overhead.
nchar(n)
A fixed-length Unicode character string data type storing 0 to 4000 characters; padded with trailing spaces if the entry is shorter than the specified length.
nvarchar(n)
A variable-length Unicode character string data type supporting international character sets and diacritics, storing up to 4000 characters.
datetime
A date and time data type spanning from 1753-01-01 to 9999-12-31 with a fractional second precision of 3.33 milliseconds, using 8 bytes of storage.
datetime2
An extended date and time data type spanning from 0001-01-01 to 9999-12-31 with a fractional second precision up to 100 nanoseconds, using 6 to 8 bytes of storage.
smalldatetime
A date and time data type spanning from 1900-01-01 to 2079-06-06 with a precision of 1 minute, using 4 bytes of storage.
Primary Key (Cheie primară)
A column or combination of columns in a table that uniquely identifies each row; it cannot accept NULL values and must contain unique data.
Foreign Key (Cheie externă)
A column or combination of columns in a table that references the primary key of another table, establishing a relationship and enforcing referential integrity.
Composite Key
A primary key composed of two or more columns that together uniquely identify a row in a table.
Constraint
A rule or boundary defined on a database column or table to guarantee the validity, accuracy, and integrity of the stored data.
One-to-One Relationship
A relational database connection where each row in one table corresponds to exactly one row in another table (e.g., an employee and their passport).
One-to-Many Relationship
A relational database connection where one record in a table can relate to multiple records in another table (e.g., one customer placing multiple orders).
Many-to-Many Relationship
A relationship where multiple rows in one table relate to multiple rows in another table, resolved in relational modeling through a junction table with foreign keys.
Data Definition Language (DDL)
A subset of SQL used to define, alter, and delete database structures and objects (e.g., CREATE, ALTER, DROP, TRUNCATE).
Data Manipulation Language (DML)
A subset of SQL used to manage and modify the records stored within database tables (e.g., INSERT, UPDATE, DELETE).
Data Control Language (DCL)
A subset of SQL used to manage user security, rights, and permissions over database objects (e.g., GRANT, DENY, REVOKE).
Data Query Language (DQL / DRL)
The component of SQL specifically focused on querying and retrieving data from database tables, centered around the SELECT statement.
GO Command
A command utilized in SQL Server utilities to terminate and execute a batch of Transact-SQL statements together.
DISTINCT
A SELECT clause keyword used to remove duplicate records from the query output, returning only unique values across specified columns.
TOP
A SELECT clause keyword used to limit the total number or percentage of rows returned from the query output.
CHARINDEX
A string function that returns the starting integer index of a specified substring within a target string expression.
CAST
A function that explicitly converts an expression of one data type into another designated data type.
CASE Statement
A SQL expression that introduces conditional IF-THEN-ELSE logic into queries to return specific values based on evaluated conditions.
ORDER BY
A query clause used to sort the result set in ascending (ASC) or descending (DESC) order based on one or more designated columns.
WHERE Clause
A query clause used to filter rows based on specific conditions before any grouping or aggregate functions are performed.
LIKE Operator
A comparison operator used in a WHERE clause to search for a specified pattern in a string column using wildcards (%, _, [], [^]).
GROUP BY
A clause that gathers rows with identical values in specified columns into summary rows, permitting aggregate functions to be executed per group.
HAVING Clause
A clause used to filter groups formed by a GROUP BY clause based on conditions applied to the outputs of aggregate functions.
INNER JOIN
A join operation that retrieves only rows having matching values across both joined tables.
LEFT JOIN (LEFT OUTER JOIN)
A join operation that retrieves all rows from the left table alongside matched rows from the right table, placing NULL values where right-table matches are absent.
FULL JOIN (FULL OUTER JOIN)
A join operation that outputs all rows whenever there is a match in either the left or right table, supplying NULL values for unmatched records from either table.
CROSS JOIN (Cartesian Product)
A join operation that produces the Cartesian product of two tables, pairing every single row in the first table with every row in the second table.
UNION
A set operator that combines the output datasets of two or more queries into a single result set while automatically removing duplicate rows.
UNION ALL
A set operator that combines the output datasets of two or more queries into a single result set while preserving all duplicate rows.
INTERSECT
A set operator that returns only the distinct rows that are shared in common between the outputs of two SELECT queries.
EXCEPT
A set operator that returns the distinct rows from the first query that do not appear in the results of the second query.
Subquery
A SELECT query that is nested inside another SELECT, INSERT, UPDATE, or DELETE statement.
Conceptual Database Design (Proiectarea conceptuală)
The preliminary phase of database design that builds an abstract model defining primary business entities and their relationships, typically illustrated using an Entity-Relationship (ER) diagram.
Logical Database Design (Proiectarea logică)
The database design phase where the conceptual model is converted into logical tables, foreign keys, and relationships, applying normalization to minimize redundancy.
Physical Database Design (Proiectarea fizică)
The database design phase where the logical model is implemented into concrete DBMS storage structures, specific data types, column sizes, and indexes.
Normalization (Normalizare)
The formal database process of eliminating redundancies and unwanted dependencies to optimize storage size and safeguard referential integrity.
First Normal Form (1NF)
A normalization level where each column contains atomic (indivisible) values, table cells hold single values, and no repeating groups exist.
Second Normal Form (2NF)
A normalization level where a table meets 1NF criteria and has no partial functional dependencies, meaning every non-key attribute is dependent on the entirety of the primary key.
Third Normal Form (3NF)
A normalization level where a table meets 2NF criteria and contains no transitive dependencies, meaning non-key attributes depend solely on the primary key.
Denormalization
The deliberate addition of redundant data into a normalized schema to reduce table joins and accelerate query read performance.
TRUNCATE TABLE
A DDL statement that rapidly empties all records from a table without logging individual row deletions, resets IDENTITY values, and preserves the table structure.
DROP TABLE
A DDL statement that permanently removes a table along with its data, indexes, triggers, constraints, and permissions from the database.
IDENTITY Property
A property configured on a numeric column that generates sequential, auto-incrementing numbers automatically as new rows are inserted.
CHECK Constraint
A table constraint that enforces domain integrity by limiting allowable column values according to a logical boolean expression.
DEFAULT Constraint
A constraint that supplies a predetermined default value into a column whenever an INSERT operation omits that column.
Clustered Index (Index Clusterizat)
An index structure that physically sorts and stores table rows in order based on key values; its leaf nodes hold the actual data, and a table can have only one.
Nonclustered Index (Index Non-Clusterizat)
An index structure stored separately from the table data, containing ordered key values and row pointers linking to the physical rows in the table.
View (Vizualizare)
A virtual table defined by an underlying SELECT query that combines or filters columns from one or more base tables without storing data itself.
Stored Procedure (Procedură stocată)
A precompiled, named collection of SQL statements saved in the database that can take input/output parameters, execute business logic, and be invoked on demand via the EXEC command.
User-Defined Function (UDF)
A reusable database routine that accepts parameters, executes logic, and must return a scalar value or a table, which can be called directly within queries.
sysadmin
A fixed server-level role granting members unrestricted permissions to perform any action across the entire SQL Server instance.
db_owner
A fixed database-level role providing complete administrative and configuration permissions within an individual database.
db_datareader
A fixed database-level role allowing users to read all data from all user tables and views within that database.
WITH GRANT OPTION
A permission clause that allows a recipient user or role to grant that specific database permission to other users.
Full Backup
A complete backup copy of the entire database, capturing all data, structures, and schemas at the point in time the backup completes.
Differential Backup
A backup that captures and saves only the data modified since the most recent full database backup.
Simple Recovery Model
A database recovery model that reclaims log space automatically without retaining transaction logs, eliminating log backups and preventing point-in-time recovery.
Full Recovery Model
A database recovery model that logs all database transactions and preserves them until backed up, enabling transaction log backups and point-in-time restore.
Transaction (Tranzacție SQL)
An atomic, logical unit of database work consisting of one or more operations that must either fully complete (COMMIT) or be completely undone (ROLLBACK).
Cascade Delete (ON DELETE CASCADE)
A foreign key referential integrity option that automatically deletes referencing child records whenever their associated parent record is deleted.