A relational database management system (RDBMS) is a collection of programs and capabilities that enable IT teams and others to create, update, administer and otherwise interact with a relational database. Most commercial RDBMSes use Structured Query Language (SQL) to access the database, although SQL was invented after the initial development of the relational model and is not necessary for its use.
RDBMS Overview
In my career I’ve use Sql Server versions from 2008 to 2014 90% of the time. However there have been a few occasions where I’ve needed to do processing using Oracles RDBMS specificially oracle 11g.
I’ve not only used the majority of the Data Manipulation Language (DML) commands in queries as well as inserting or updating in ad hoc reporting but I’ve also used them in in-line sequel statements, stored procedures, in program data manipulation of MVC, Winforms, Web Api’s, WPF and WCF type applications.
Although in larger shops the responsibilities for creating Data Definition Language (DDL) commands normally fall under the control of Database Administrators (DBA) in smaller shops that role falls to the developer. That is why I am quite familiar with most of those commands, such as CREATE, ALTER, DROP, TRUNCATE AND COMMENT and RENAME
Tools
The 2 tools I’ve used when either doing DML or DDL database commands have been Sequel Server Management Studio (SSMS) for sequel server and SQL Developer for Oracle.
SQL Server Profiler
One of the more interesting and useful tools in SSMS is the SQL Server Profiler.
An SQL server profiler is a tool for tracing, recreating, and troubleshooting problems in MS SQL Server, Microsoft’s Relational Database Management System (RDBMS). The profiler lets developers and Database Administrators (DBAs) create and handle traces and replay and analyze trace results. In a nutshell, it’s like a dashboard that shows the health of an instance of MS SQL Server.
It works by giving DBAs and developers a high-level view of the operation of a system. Users create traces to capture data and monitor errors and other problems.
Below is an example of the profiler in action. A web page makes a database call to the database server to retrieve data. The profiler monitored the command and produced a trace with which the results could be analyzed. If there were one or more errors either with query or the database it would be shown in the trace results.
Benefits
Below is a short list of the benefits of this tool to both developers and Database Administrators (DBAs).
- Clarity. It can reveal how an instance works when it’s interacting with a client.
- Troubleshoot problems. It can help zero in on trouble spots by allowing us to capture and replay key events. This function also helps with stress testing and identifying slowly executing queries.
- Allow non-administrator users to create traces securely. It can cater to the needs of DBAs, developers, database designers, business intelligence specialists, IT professionals, and even accountants.
- Compare activity to baselines. It lets users save trace data and compare it to newer data to spotlight new trouble spots.
- Capture traces for Transact-SQL, SSIS, and Analysis Services.
Database Design
Information is a valuable organizational resource it should be organized, stored, secured, and should be readily available in an accessible format for daily operations and analysis by individuals, groups, and processes.
A good database design should adhere to the following objectives:
- Security
- Availability
- Data Integrity
- Performance
- Usability
- Extensibility
A useful tool built into SQL Server Management Studio (SSMS) when doing database design is the creation and study of a Database Diagram (DD). A DD allows the dba or developer to visualize a database structure for further analysis.
For example I was tasked with coming up with an additional set of tables, views, stored procedures, functions, triggers..etc. for an enhancement to in-house web application. To get a high level view of the current state of the data store I created a DD. Below is an example of how I did it using a sample database.
Lamont Carter 