Relational Database Management System (RDBMS) Concepts Using SQL Server 2008 R2
An instance of SQL Server can be stopped and started from SQL Server Configuration Manager.
By default, only members of the local administrators group can start, stop, pause, resume or restart a service. (The process is similar on other versions of Windows.)
To start the default instance of SQL Server
1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, in the left pane, click SQL Server Services.
3. In the details pane, right-click SQL Server (MSSQLServer), and then click Start. A green arrow on the icon next to the server name and on the toolbar indicates that the server started successfully.
4. Click OK to close SQL Server Configuration Manager.
To start a named instance of SQL Server
1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, in the left pane, click SQL Server.
3. In the details pane, right-click the named instance of SQL Server, and then click Start. A green arrow on the icon next to the server name and on the toolbar indicates that the server started successfully.
4. Click OK to close SQL Server Configuration Manager.
To start an instance of SQL Server with startup options
1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, in the left pane, click SQL Server.
3. In the details pane, right-click the instance of SQL Server, and then click Properties.
4. In the SQL Server (<instancename>) Properties dialog box, click the Advanced tab, and then click Startup Parameters.
5. At the end of the original text, in the Value column, type the startup parameters you want, and then click OK. Separate parameters with a semi-colon, for example, –c;-m.
6. Stop and restart SQL Server for the parameters to take effect.
SQL Server running on a cluster is best managed by using Cluster Administrator.
Database - is a collection of data that is organized such that its contents can be easily accessed, managed, and updated.
Database Management System - is an electronic filing cabinet that stores data, and also allows you to add, delete, modify and retrieve data.
Benefits of a Database System:
1. Reduces Data Redundancy - reduces duplication of data.
2. Improves Data Consistency - any changes to the data have to be performed at a single place.
3. Enables Data Sharing
4. Aids Standardization - ensures that standards in the representation of data can be set and followed.
5. Maintains Data Integrity - refers to the accuracy of data in the database.
6. Ensures Data Security - DBA or Database Administrator can place restrictions on the data to ensure its securtiy.
Data Model - describes a containter for storing data, and the process of storing and retrieving data from that container.
Data Model Evolution:
1. Flat-file Data Model - consists of only one table or file.
- cannot handle very complex data, and can cause redundancy when data is repeated more than once.
2. Hierarchical Data Model - data is arranged as an inverted tree of data.
- relationships are thought of in terms of children and parents.
- a parent may have multiple childred, but a child can only have one parent.
- to ensures systematic access to the data, you have to start at the root, and travel down the tree until you reach your target, in order to get to a low-level table.
3. Network Data Model - similar to Hierarchical Data Model.
- data is stored in sets, instead of the hierarchical tree format.
- parents can have multiple children, and children can have multiple parents.
- supports many-to-many relationships.
4. Relational Data Model - based on two branches of mathematics - 'Set Theory' and 'Order Predicate Logic'.
- 'Relation' is derived from the 'Set Theory'.
- data is stored in tables.
- a table has a unique name that identifies its contents.
- a table must have a column, or a combination of columns that uniquely identifies each row called Primary Key.
- connecting two tables on a common field is referred to as, "establishin relations".
- Foreign Key, a column or combination of columns whose value match the primary or unique key in another table, links tables.
RDBMS (Relational Database Management System) - is a suite of software programs for creating, maintaining, modifying, and manipulating a relational database, and can also be used to craeate an application for the user to interact with the data stored in the database.
RDBMS Users:
1. End User - invokes an application to interact with the system, or writes a query for easy retrieval, modification, or deletion of data.
2. Application Programmer - writes a programs that end users employ to access the database.
3. Database Administrator - is primarily concerned with creating and maintaining the database, ensuring its proper functioning, and implementing the security of the database.
Designing a Database - ER (Entity Relationship) Model:
1. Prior to the actual design of a model, the Dababase Designer and the end user decide on the data to be stored in the database.
2. The Database Designer collects and collated data from bills, reports, forms, and other records.
3. With the help of the end user, the designer then checks the operational, and data processing needs of the organization.
4. Then, the redundant data is removed.
5. The designer identifies the tables, the fields in each table, and the relationships between different tables using ERD.
ER Model Components:
1. Entity - a person, place, thing, object, event, or even a concept, which can be distinctly identified.
2. Attributes - properties or characteristics of an entity.
3. Relationship - an association among entities.
Entity Relationship Diagram (ERD) - graphically represent the entities, the attributes of an entity, and the relationships between these entities.
Four Types of Relationsips:
1. One to One - 1:1
2. One to Many - 1:N
3. Many to One - N:1
4. Many to Many - M:N
The ERD should be drawn before designing the table, as the entities that the database designer identifies form the tables, while their attributes become the columns.
Normalization - is the process of removing the redundancies from the incoming data.
Normal Forms - are rules for normalization that dictate the creation of a relational database.
First Normal Form: No Repeating Elements or Groups of Elements:
1. A row of data cannot contain repeating groups of similar data (atomicity); and
2. Each row of data must have a unique identifier (or Primary Key).
Second Normal Form: No Partial Dependencies on a Concatenated Key:
- For a table that has a concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. If any column only depends upon one part of the concatenated key, then we say that the entire table has failed Second Normal Form and we must create another table to rectify the failure.
Third Normal Form: No Dependencies on Non-Key Attributes:
- Eliminate fields that do not depend on the key.
Boyce-Codd Normal Form (BCNF):
- a table should be in the third normal form, and all candidates keys defined for the table should satisfy the test for the third normal form.
Fourth Normal Form:
- a relation must first be in Boyce-Codd Normal Form. Additionally, a given relation may not contain more than one multivalued dependency.
Fifth Normal Form:
- deals with cases where information can be reconstructed from smaller pieces of information that can be maintained with less redundancy.
Characteristics of a Normalized Database:
1. Each table has a key field.
2. There are no repeating fields.
3. Each table contains information about a single entity.
4. Each field in a table depends on the key field.
5. All non-key fields are mutually independent.
Normalization Drawbacks:
1. Complex join queries have to be coded to access data in multiple tables.
2. The CPU time required to resolve complex join quieries will be very high. If such joins are used very often, the performance of the database will become very poor.
Tables - are database objects that contain the data stored in a database.
Features of SQL Server Tables:
1. Each table has a unique name in the database.
2. Each row in a table is unique, and describes one set of related information about the subject of the table. The Primary Key ensures the uniqueness of each row.
3. Each column or field describes a single characteristic of an entity. Column names are unique within a table.
4. The order of rows or columns is not significant.
Designing an SQL Server Table:
1. What are the names of the columns to be stored in the table, and what is their data type, and length?
2. Which column accept Null values?
3 Are default values and rules to be used? Where should they be used?
4. What are the types of indexes required?
5. Which columns are Primary or Foreign Keys?
Creating a Table:
1. Start > All Programs > Microsoft SQL Server 2008 R2 > SQL server Management Studio
2. Connect to a Server
3. Click Databases and select a database
4. Click New Query
5. Type the following:
create table Airline_Master (Aircode char(2), Airline_name varchar(15))
6. Click !Execute
Modifying a Table:
alter table Airline_Master add NoOfAircrafts int
alter table service alter column service_code varchar(2)
Deleting Column:
alter table flight drop column type
Adding Rows:
insert into Airline_Master values ('A1', 'Airlines India', 25)
Updating Rows:
update Airline_Master set Airline_name = 'Air India' where Aircode = 'A1'
update passenger set sex='F' where sex='female'
Deleting Rows:
delete from Airline_Master where Aircode = 'A1'
delete from airline_meal where meal_code='CONV' and aircode='AI'
Viewing the Table:
sp_help Airline_Master
sp_help is a system-stored procedure that displays the information on a database object, user-defined data type, or system data type.
select * from Airline_Master
select aircraft_code, fare, fare*1.15 as fare_15_markup from flight_details where aircraft_code='IC01'
Removing a Table:
drop table Airline_Master
Creating Databases Using SQL Server Management Studio:
1. Start > All Programs > Microsoft SQL Server 2008 R2 > SQL server Management Studio
2. Connect to a Server on which the database has to be created
3. Right-click Databases > New Database...
4. Type Flights as the database Name
5. Click OK
6. Double click the Databases folder in the right pane.
Modifying Databases:
1. Expanding the data or transaction log space allocated to the database.
2. Shrinking the data and transaction log space allocated to the database.
3. Creating filegroups.
4. Changing the name of the database.
Increasing the Size of a Database Using SQL Server Management Studio:
1. Right-click on the Flight Information database.
2. Properties > General
3. Enter 5 in the Size under Database in the right pane.
4. Click OK.
Deleteing Databases Using SQL Server Management Studio:
1. Right-click on the Flight Information database.
2. Delete > OK
Case Study:
Let us consider the case of an Airline Reservation system. This system has the following objectives:
1. To handle a large database.
2. To enable data sharing with other airline offices across the world.
3. To carry out on-line processing.
We will design and develop our system, based on these objectives. The details of the actual system are given below.
Fly Safe Airways International is in the business of providing airline reservations. Reservations are made for several airlines. Passengers can be booked in one of
three classes - First, Executive, and Economy. Special services, such as, child-care, and a nurse are provided on the flights. Wheel chairs are also provided at an
additional charge.
The company wants to computerize its operations in the following areas:
1. Reservations
2. Cancellations
Note: As of the moment, Query Analyzer, which was present in SQL Server 2000, was removed Microsoft in SQL Server 2008 R2.
