TOOLS: SQL Clients AKA DB Tools

A SQL client is, as the name indicates, a client for a SQL Relational Database Management System (RDBMS). It can help you manage (create, read, modify/update, delete) DBs.
Some kind of administration tool is available with each RDBMS (you could have it installed on your computer or only on the server). But sometimes you'll have productivity gains if you use a multi-DB client because using the same SW user interface you will be managing several different DB engines / instances.

Examples of DB Tools include: Aqua Data Studio (Windows, proprietary - this one scripts data into DML SQL statements), Squirrel SQL Client (open source, java-based).

A comparison of DB tools can be found - where else? - in Wikipedia so choose one according to your requirements (and that might be shaped by your budget): 
https://en.wikipedia.org/wiki/Comparison_of_database_tools


For SQL Server, as per 2016-11: 
SQL Server Management Studio (SSMS)
https://msdn.microsoft.com/en-us/library/mt238290.aspx 
SQL Server Management Studio Express

https://technet.microsoft.com/en-us/library/ms365247(v=sql.105).aspx
And a related download - SQL Server 2016 Express (the free DB engine):
https://www.microsoft.com/en-us/download/details.aspx?id=52679


(Updates: @2016-11-08: MS SQL Server example links)

Software Design: Design Principles (and how they could help us in partitioning the system)

With a good decomposition (into modules and submodules):

• The overall system Design is improved (anticipating and solving earlier performance or scalability bottlenecks, better preparation for change and evolution, etc.) and... 
• It is easier to maintain (Maintainability) as well as... 
• It easier to Develop (during the following activities of software construction: easier to split tasks between developers, easier to integrate new technologies and new team members, easier to forecast the impact of new requirements with good accuracy, etc.). 

But how exactly should it be performed? For some principles, jump here (*).

CHANGE IS PART OF LIFE: So we should design systems that cope with it "elegantly"
- The customer can change it's mind
- The market needs can change during the project (with impact on our scope)
- We could have got requirements wrong and have the need to rework them (as well as all dependencies on them, etc.)
- Reused components could have to be replaced (due to lack of support, stability, etc.) along the way
- etc.

Modules

A module can be defined variously, but generally must be a component of a larger system, and operate within that system more or less independently from the operations of the other components of the system. A module is a subdivision of complexity that has evident benefits.

Decomposition Levels

• The first partition shall be determined by selection or design of an Architectural Archetype (system type, e.g. Web App)

• The archetype establishes tier and layer separation and, in the physical tier perspective, a clear and unavoidable packaging and coupling isolation criterion.

• The archetype is a structural technical view of the solution regardless of business domain and particularities. As such, it is a very stable and reusable specification.

• The archetype is also a technological determinant decision as it clearly points to the usage of specific technologies, frameworks, development environments, etc.

• The second level partition shall address decomposition of the archetype’s parts into Package Components (UML Package Diagrams; package = namespaces that group classes and interfaces)

• The Packaging Components represent the binary outputs of the solution (in .Net for instance these correspond to the .EXE and .DLL)

• Their decomposition criteria are based on coupling, deployment and tier placement.

• The Packaging Component represents a single reference point for the functionality and is an aggregation for further decomposition (Component).

• The third level partition shall address decomposition of the Package Components into Components and Interfaces and establish third-party dependencies (reuse process) - UML Component Diagrams

• Remember that Components may be nested into each other to produce sub-levels of decomposition and reduce complexity of the overviews.

• This is a pure conceptual view that is not directly concerned with packaging and deployment.

• This view is oriented by “information-hiding” in Parnas sense. The goal here is to represent the system with as few major concepts as possible. Of course each of those concepts may be further detailed (by sub-components) as needed.

• The fourth level partition shall address Component decomposition in Classes - Detailed Design; physical data tables could be also modeled (it the system is database-oriented) and traced to classes.

All elements of the decomposition should trace to the upper level elements they are related to (traceability) in the analysis model.

Principles for module decomposition 

• Strong cohesion and loose coupling (goals to be achieved): 

• Cohesion is a measure of how strongly related and focused the responsibilities of a single module are. That is, how homogeneous and simple is the goal of a software module.

• Coupling (or dependency) is the degree to which each program module depends on each other module.

• Package Cohesion Principles: Classes are a necessary, but insufficient, means of organizing a design. The larger granularity of packages is needed to help bring order. But how do we choose which classes belong in which packages

• Release Reuse Equivalency Principle: The granule of reuse is the granule of release (and that should be the package).

• Common Closure Principle: Classes that change together, belong together.

• Common Reuse Principle: Classes that aren’t reused together should not be grouped together.

• Package Coupling Principles: Applications tend to be large networks of interrelated packages. The rules that govern these interrelationship are some of the most important rules in object oriented architecture

• Acyclic Dependencies Principle: The dependencies between packages must not form cycles.

• Stable Dependencies Principle: Depend in the direction of stability.

• Stable Abstractions Principle: Stable packages should be abstract packages.

Architectural Principles

• Abstraction gears towards comprehension
• Encapsulation gears towards protection from outside interference
• Information Hiding gears towards containment of design changes
• Open Closed Principle – OCP: “A module should be open for extension but closed for modification.” i.e. “We should write our modules so that they can be extended, without requiring them to be modified. In other words, we want to be able to change what the modules do, without changing the source code of the modules.”
• Inversion of Control (or Dependency Inversion Principle) is an important object-oriented programming principle that can be used to reduce coupling inherent in computer programs: “Depend upon Abstractions. Do not depend upon concretions.” [Martin]
• Interface Segregation Principle – ISP: “Many client specific interfaces are better than one general purpose interface” i.e. “If you have a class that has several clients, rather than loading the class with all the methods that the clients need, create specific interfaces for each client and multiply inherit them into the class.” [Martin]

Along with OOP Principles, some Design Patterns (which is a well known, very good solution for a recurrent problem) are also very useful while performing the decomposition of a system.

(*) Additional References and Further Reading

• http://ifacethoughts.net/2006/03/24/design-principles/ (REP, DBC, etc.)
• http://ifacethoughts.net/2006/04/04/reuserelease-equivalency-principle/ (REP)
• https://sourcemaking.com/design_patterns (Design Patterns: structural and more - creational and behavioral; also remember to avoid anti-patterns)
• https://sourcemaking.com/refactoring (code smells: what to do to improve this "code smell"?)
• Design Principles and Design Patterns - Robert C. Martin - 2000
• OOP principles (if you're using OO Languages): Abstraction, Encapsulation, Information Hiding
• Additional Source: ITG M. and Uncle Google giving us the above cited results.

Also remember to learn - at a second stage - about:

• Database Design Patterns, 
• GUI Design Patterns (and UXD - User interaction), 
• Enterprise Architecture Patterns (Integration), 
• etc.

(2016-05-12: Added link for enterprise integration patterns; 2016-07-21: formatting, typos)

Process: SW Design Process and its main work products

After SW Requirements (and the SW Requirements Engineering Phases ends successfully) we'll be applying the next QMS Process: ENG.2 SW Design Process. The main goal is to define "how to do" things (requirements were focused on "what to do").

Typically all design decisions (high-level-design) are written on the Software Architecture Specification (SAS) which is commonly a deliverable.

The pertinent milestone is called the Critical Design Review (CDR) unless you have provided the customer with a SAS V1 on PDR. If you did so, you'll probably end the CDR with SAS V2. If not you'll be releasing a SAS V1 apar with the code (and the binaries needed for deploying the system on a testing environment, for the next phase, the Validation Phase).
In some QMSs, some part of the design and the code are performed in the same phase (so that if you spot a need to change de design during the code, you are still able to change it).

Other Design Documents

Sometimes customers will ask for a Software Detailed Design (SDD is a kind of a programmer's manual that describes all source files contents including for an OOP language all classes, including data members, operations, interfaces and the algorithms being implemented in those source files). If possible, align the team with the proper coding conventions and comments to be produced aling the code, in order to be possible to reverse engineer te code to a modelling tool (that enables you to build in a faster way the SDD document). Sometimes this document is called Detailed Design Description.

There are also special documents called Interface Control Documents (ICD) that come specially handy when you need to establish communications (data flows in and out) with external systems. These could be described in a section in the SAS, or sent to a separated ICD document (if you want to share just the interface control part to external departments and/or companies).

Database specification documents (DBS) are also considered detailed design documents and are typically generated from data modelling tools (or reversed engineered to a model and then to a document) for publication (to the customer, to the developer/maintenance teams).

This is where you want to define key technologies to use (what DB Server, what Application Server, what OS) up to the version if needed and the last great opportunity to think on reuse (COTS components, open source components, in-house components, etc.)

Main Activities (SW Design)

Mainly we'll be doing:

• Architectural requirements (completing the requirements catalog with non-functional - or other organizational and even functional - requirements); Architectural Requirements are a subset of the user, system and software requirements, determined by architectural relevance.
• Architecture specification:
• Defining the meta-architecture options: principle, style, key-mechanisms 
• Defining the conceptual architecture: Partition system, allocate responsibilities
• Defining the logical architecture: Model collaborations, design interfaces
• Architecture Validation: As in all things in life, we'll be peer reviewing the SAS document, and before that, we'll be brain-storming key decisions and thinking on Reuse (remember to use DAR).

You can find the process definition here.
For a good online resource on architecting remember to look at the The Visual Architecting Process (VAP, by Ruth Malan and Dana Bredemeyer, 2002)