MTA Exam 98-364: Database Fundamentals

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/84

flashcard set

Earn XP

Description and Tags

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.

85 Terms

1
New cards

Database (Bază de date)

A structured collection of data stored according to specific rules that govern access, management, and updating of the information.

2
New cards

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.

3
New cards

SQL Server

A relational database management system (RDBMS) developed by Microsoft.

4
New cards

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.

5
New cards

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).

6
New cards

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).

7
New cards

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.

8
New cards

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.

9
New cards

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.

10
New cards

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.

11
New cards

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.

12
New cards

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.

13
New cards

Field (Câmp)

The intersection of a row and a column that holds a single, specific attribute value for a given record.

14
New cards

bit

An exact integer data type that can take a value of 0, 1, or NULL, using 1 byte of storage.

15
New cards

tinyint

An exact integer data type that stores whole numbers from 0 to 255, using 1 byte of storage.

16
New cards

smallint

An exact integer data type that stores whole numbers from -32,768 to 32,767, using 2 bytes of storage.

17
New cards

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.

18
New cards

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.

19
New cards

decimal(p,s) / numeric(p,s)

Exact numeric data types where pp specifies precision (total number of digits) and ss specifies scale (digits to the right of the decimal point), storing numbers from −1038+1-10^{38}+1 to 1038−110^{38}-1 using 5-17 bytes.

20
New cards

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 (' ').

21
New cards

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.

22
New cards

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.

23
New cards

nvarchar(n)

A variable-length Unicode character string data type supporting international character sets and diacritics, storing up to 4000 characters.

24
New cards

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.

25
New cards

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.

26
New cards

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.

27
New cards

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.

28
New cards

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.

29
New cards

Composite Key

A primary key composed of two or more columns that together uniquely identify a row in a table.

30
New cards

Constraint

A rule or boundary defined on a database column or table to guarantee the validity, accuracy, and integrity of the stored data.

31
New cards

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).

32
New cards

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).

33
New cards

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.

34
New cards

Data Definition Language (DDL)

A subset of SQL used to define, alter, and delete database structures and objects (e.g., CREATE, ALTER, DROP, TRUNCATE).

35
New cards

Data Manipulation Language (DML)

A subset of SQL used to manage and modify the records stored within database tables (e.g., INSERT, UPDATE, DELETE).

36
New cards

Data Control Language (DCL)

A subset of SQL used to manage user security, rights, and permissions over database objects (e.g., GRANT, DENY, REVOKE).

37
New cards

Data Query Language (DQL / DRL)

The component of SQL specifically focused on querying and retrieving data from database tables, centered around the SELECT statement.

38
New cards

GO Command

A command utilized in SQL Server utilities to terminate and execute a batch of Transact-SQL statements together.

39
New cards

DISTINCT

A SELECT clause keyword used to remove duplicate records from the query output, returning only unique values across specified columns.

40
New cards

TOP

A SELECT clause keyword used to limit the total number or percentage of rows returned from the query output.

41
New cards

CHARINDEX

A string function that returns the starting integer index of a specified substring within a target string expression.

42
New cards

CAST

A function that explicitly converts an expression of one data type into another designated data type.

43
New cards

CASE Statement

A SQL expression that introduces conditional IF-THEN-ELSE logic into queries to return specific values based on evaluated conditions.

44
New cards

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.

45
New cards

WHERE Clause

A query clause used to filter rows based on specific conditions before any grouping or aggregate functions are performed.

46
New cards

LIKE Operator

A comparison operator used in a WHERE clause to search for a specified pattern in a string column using wildcards (%, _, [], [^]).

47
New cards

GROUP BY

A clause that gathers rows with identical values in specified columns into summary rows, permitting aggregate functions to be executed per group.

48
New cards

HAVING Clause

A clause used to filter groups formed by a GROUP BY clause based on conditions applied to the outputs of aggregate functions.

49
New cards

INNER JOIN

A join operation that retrieves only rows having matching values across both joined tables.

50
New cards

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.

51
New cards

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.

52
New cards

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.

53
New cards

UNION

A set operator that combines the output datasets of two or more queries into a single result set while automatically removing duplicate rows.

54
New cards

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.

55
New cards

INTERSECT

A set operator that returns only the distinct rows that are shared in common between the outputs of two SELECT queries.

56
New cards

EXCEPT

A set operator that returns the distinct rows from the first query that do not appear in the results of the second query.

57
New cards

Subquery

A SELECT query that is nested inside another SELECT, INSERT, UPDATE, or DELETE statement.

58
New cards

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.

59
New cards

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.

60
New cards

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.

61
New cards

Normalization (Normalizare)

The formal database process of eliminating redundancies and unwanted dependencies to optimize storage size and safeguard referential integrity.

62
New cards

First Normal Form (1NF)

A normalization level where each column contains atomic (indivisible) values, table cells hold single values, and no repeating groups exist.

63
New cards

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.

64
New cards

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.

65
New cards

Denormalization

The deliberate addition of redundant data into a normalized schema to reduce table joins and accelerate query read performance.

66
New cards

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.

67
New cards

DROP TABLE

A DDL statement that permanently removes a table along with its data, indexes, triggers, constraints, and permissions from the database.

68
New cards

IDENTITY Property

A property configured on a numeric column that generates sequential, auto-incrementing numbers automatically as new rows are inserted.

69
New cards

CHECK Constraint

A table constraint that enforces domain integrity by limiting allowable column values according to a logical boolean expression.

70
New cards

DEFAULT Constraint

A constraint that supplies a predetermined default value into a column whenever an INSERT operation omits that column.

71
New cards

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.

72
New cards

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.

73
New cards

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.

74
New cards

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.

75
New cards

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.

76
New cards

sysadmin

A fixed server-level role granting members unrestricted permissions to perform any action across the entire SQL Server instance.

77
New cards

db_owner

A fixed database-level role providing complete administrative and configuration permissions within an individual database.

78
New cards

db_datareader

A fixed database-level role allowing users to read all data from all user tables and views within that database.

79
New cards

WITH GRANT OPTION

A permission clause that allows a recipient user or role to grant that specific database permission to other users.

80
New cards

Full Backup

A complete backup copy of the entire database, capturing all data, structures, and schemas at the point in time the backup completes.

81
New cards

Differential Backup

A backup that captures and saves only the data modified since the most recent full database backup.

82
New cards

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.

83
New cards

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.

84
New cards

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).

85
New cards

Cascade Delete (ON DELETE CASCADE)

A foreign key referential integrity option that automatically deletes referencing child records whenever their associated parent record is deleted.