Database Management

What does IDE stand for?

Integrated Development Environment

What is SQL server's Management Studio for?

It is a client-side IDE that is used by database developers and administrators to communicate with the SQL server (DBMS)

What is a "stored procedure" and why do you need it?

It is a pre-written and pre-compiled piece of SQL code residing on the Database Server. By being pre-compiled, it reduces processing time and execution It also allows several applications to share the same piece of SQL code

The four MS system databases

Master, Model, Msdb, Tempdb

Examples of relational DBMS

Oracle, MS Access, MS SQL Server, SQLite, MySQL, Cassandra

What is the "presentation layer" responsible for?

Presenting the user interface to the user Sending requests to the application

What are referential constraints?

Rules that define the relationships of the entities

DBMS features

Security system (data encryption) Data validation Backup and Data Recovery Performance Optimization Monitoring user access and performance

DBMS user categories:

System administrator Database administrator Application developer End user

What is in a Database Server?

The DBMS along with the database that it manages

What is the heart of the database server and what does it do?

The Database engine It handles all the relational database work and receives instructions from SQL on what needs to be done

What does a Database Administrator need to create the physical database schema?

a DBMS

What does an application need to insert, update, delete and query data into the database (using SQL statements)

a DBMS

What does DBMS provide?

a systematic way to create, retrieve, update and manage data

What is Information System Architecture?

a useful logical framework showing where elements of an information system are located.

What does the Application developer do?

designs and programs applications, an as part of that, develops database designs and schemas

What does Referential Integrity do?

helps ensure that relationships between records are valid, and that related data is not accidentally deleted, or inconsistently updated.

What does the System administrator do?

maintains the overall IT system and infrastructure, including DBMS software and hardware

Which system architecture (in which DBMS is implemented in) do you use for a large number of users?

multi-tier architecture

In multi-tier architectures, what do n=1 and n=2 stand for?

n=1 stands for mainframe n=2 stands for client-server

What are the features of SQL Server Management Studio

presents a graphical interface for: 1. monitoring and administering instances of SQL Server 2. deployment, monitoring and upgrading of data-tier components used by your applications

What does the End user do?

provides the data that is saved into the database and queries the data from the database

What does 3-tier Architecture (n=3) do?

separates the three tiers which are the user's device (presentation tier), the application server (business logic tier), and the database server (data access tier)

What does DBMS allow users to do?

-Define they type of data that will be stored, and the structure in which it will be organized. -Maintain the data in the database. -Extract information from the database that matches some conditions -View only sections of the database that the user is interested in or is authorized to access. -Produces summary reports based on the information in the database -Perform calculations on the data.

What does MS SQL Server have to offer?

-Fast and reliable database storage -transaction processing -data warehousing -analytics features -cloud-based solutions

What are the ISA model's three tiers?

-User Interface or presentation tier -Logical processing or business logic tier -Data or data access tier

What does the Database administrator do?

-evaluates database designs -creates, alters and drops databases based on given schemas -migrates data from legacy systems into a new database -assigns access rights to users

Give the components of a SQL server.

1. Database Engine 2. Analysis services 3. Reporting services 4. Integration services 5. Master Data services 6. R Services

Examples of common DBMS and give their short descriptions

1. MySQL: an open source 2. DBMS (most popular) 3. SQLite: a lightweight DBMS 4. Oracle: a relational DBMS that runs on most major platforms 5. MS SQL Server: a relational DBMS by MS 6. MS Access: DBMS designed by MS for desktop apps 7. PostgreSQL: an object-relational DBMS 8. MongoDB: a document based DBMS (top NoSQL) 9. Cassandra: a NoSQL, decentralized, distributed, column-oriented database engine

RI settings you can define in case of insertions

1. Prevent the insertion of a record without a parent record in the reference table (enforces Referential Integrity) 2. Allow the insertion of a record with a NULL reference

RI settings you can define in case of updates to the primary key attribute referred to in a relationship

1. Prevent the update of a primary key referred to as foreign key in the other table (RI enforced) 2. Cascade update the value of the foreign key at the same time as the primary key

Referring to Referential Integrity, what are the three possible settings you can define in case of deletion attempts?

1. Prevention deletion (enforces referential integrity) 2. Cascade-delete (deleting the related records) 3. Nullify (Nullifies the foreign key in the related record)

What is the "data access" layer responsible for?

1. Processing data requests received from the application 2. Enforcing database rules and constraints 3. Enforcing data security and data consistency 4. Performing data intensive processing

What is the "application/business logic layer" responsible for?

1. Processing requests received from the client 2. Applying business logic and performing computations 3. Sending data manipulation requests to the DBMS 4. Returning responses in appropriate format to the presentation layer

Types of database management systems

1. relational database (for relational databases) 2. NoSQL (evolving data structures) 3. in-memory database (for better performance) 4. object oriented database

What is MS SQL Server?

A multi-user relational DBMS aimed at enterprise solutions

What is DBMS

A system software used for creating and managing databases.

What does API stand for and what does it do?

Application Programming Interface enables users and other applications to access the data in the database

What is DBMS data manipulation?

Creating, reading, updating or deleting data that can be performed either globally or based on certain conditions that the data must match.

What is querying the database?

Extracting information from a database