Mapping ER Models into Relations (For Students of Database Systems)

I was about to write something on this topic but found a resource that contains the contents I was willing to discuss.
Do visit the link below and get the copy of material for future reference
http://db.grussell.org/section006.html

Database Systems Lecture 1 & 2 (MAJU Spring 2012)

Topics of the Lesson:
• Introduction of Database
• File-based systems
• Database management systems
• Comparison between File-based system and Database management systems

Functions of DBMS
Introducing Database:
The simplest definition is given by Date (1999, p. 2) according to which Database is a
collection of computerized data files. In simple words it is computerized
record keeping.

Examples of Database Applications
• Purchases from the supermarket
• Purchases using your credit card
• Booking a holiday at the travel agents
• Using the local library
• Taking out insurance
• Using the Internet
• Studying at university

File-based Systems
• Collection of application programs that perform services for the end users (e.g. reports).
• Each program defines and manages its own data
• E.g. is a C++ system that accepts and stores data. In such case the sequence in which the fields are recorded is coded in the program, not in the file.

Limitations of File-Based Approach

• Separation and isolation of data
– Each program maintains its own set of data.
– Users of one program may be unaware of potentially useful data held by other programs.
– Duplication of data
– Same data is held by different programs.
– Wasted space and potentially different values and/or different formats for the same item.

• Duplication of data
– Same data is held by different programs.
– Wasted space and potentially different values and/or different formats for the same item.

Database Approach

• Arose because:
– Definition of data was embedded in application programs, rather than being stored separately and independently.
– No control over access and manipulation of data beyond that imposed by application programs.
•
Result:
– the database and Database Management System (DBMS).

Formal definition of Database
• Shared collection of logically related data (and a description of this data), designed to meet the information needs of an organization.
• System catalog (metadata) provides description of data to enable program–data independence.
• Logically related data comprises entities, attributes, and relationships of an organization’s
information.

Database Management System (DBMS)
• A software system that enables users to define, create, and maintain the database and that provides controlled access to this database.
Views
• Allows each user to have his or her own view of the database.
• A view is essentially some subset of the database.

Functions of Database Management System
· Provide Security
· Data Integrity
· Provide controlled concurrency
· Reduced data redundancy
· Crash Recovery
· Backup and Recovery

Introduction to WIX (Windows Installer XML)

Recently I have got an opportunity to work on creating installers for some of our products. In past I never got involved in post development activities but during my current job I spent some time in development related to deployment.
I have discovered there is a whole world out there to explore. Even the tip of the ice berg is quite vast.
Initially I created installers based on MSI technology using Visual Studio. However, I experienced that the approach was not friendly when it come to incorporate some product changes in installer. This is because we need to recompile the entire project and subsequently do some changes to resultant installer via orca. Addition/updation is a very common requirement in the web-based products we sell. For example, sometimes there is a small change like css update of an image/icon changes. This approach, which I was using required someone to rebuild the Visual Studio project which implies that visual studio is present on the machine. In addition, another dependency exists in the form of a person who knows visual studio and has some experience of creating installers.
Eventually a need of such an approach was felt which could free us from such dependencies.
The research resulted in the discovery of Microsoft WIX (Windows Installer Xml). WIX which is based on MSI technology allows us to descriptively create installers using XML files. There are tools through which people only specify the folders that need to be part of the installer and facilities are available to almost do whatever that is necessary while creating installer. Plug-ins are also available to integrate WIX with visual studio.
However, it must be kept in mind that the learning curve is quite steep. To facilitate people who are interested in learning WIX, I am listing some of the resources that I found useful. I will be adding some more resources to the list after filtering the collection I have

Creating WIX Installer for ASP.NET Web Application
http://www.codeproject.com/KB/install/asp_wix.aspx

From MSI to WiX
http://blogs.technet.com/b/alexshev/archive/2008/02/10/from-msi-to-wix.aspx

WIX Tutorial
http://wix.tramontana.co.hu/tutorial

Logging in IIS 7

Location of Log file:
%SystemDrive%\inetpub\logs\LogFiles

Pre-Requisite of Logging:
On Windows Server 2008 R2, following role service must be installed (In other Windows OS the following module must be enabled)
• Http Logging

Enabling and Disabling Logging:

If the pre requisite is installed the “Logging Icon” will appear in the “IIS” category of IIS Manager ([Server] Home Screen)
By default it is enabled, however it can be disabled or other configuration can be changed

References:
http://www.itsolutionskb.com/2009/10/how-to-find-iis-log-files/
http://technet.microsoft.com/en-us/library/cc754631(WS.10).aspx

AnjLab Profiler and “master.mdf…trc” files

SQL Profiler is a very useful tool to trace the execution of instructions over a database, however , it is not available for express edition of SQL Server databases
An alternate for this is a profiler from AnjLab which provides features quite similar to that of SQL Profiler.
Both SQL Profiler and AnjLab Profiler make use of the “Trace” facility available in the database engine of SQL Server.

Overall this profiler is harmless, however, there are two issues that must be considered when using the profiler

1. The profiler creates “master.mdf..yyyy.mm.dd.hh.min.sec.trc” file but (at least in our case it) does not delete the file when tracing is finished. These files are created in the default “DATA” folder of SQL Server instance (in our case). Since the files are not deleted automatically therefore a number of trace executions consume a lot of valuable space

2. In our scenario, if the profiler executes for a long time then it starts going into “Not Responding” states. Eventually the user is forced to close the application. In this case the “Trace” in SQL Server is not deleted thus it continues to execute and keeps on creating files unless explicitly closed using “sp_trace_setstatus”. (For more information on finding, stopping and closing traces see the article in this blog “SQL Server Trace Files Consuming Lot of Space”).

SQL Server Database becoming unavailable

Recently one of our Client went through a situation in which databases were becoming unavailable. We investigated the problem and searched for possible solutions.
Here is the summary of the activity

Findings

The error found in the client's database server log is
“FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.)”
Other messages do not seem to be suspicious
There are three popular causes of this error
Abnormal Shutdowns:
In case system abnormally shuts down due to power failure or was forced to shutdown due to a “Not responding state”, operating system processes like “Write caching on device” are not completed properly and some files are not released.
Due to this some databases are shown as suspect on restart
Solution:
Uncheck the “Enable write caching on device”, which can be found in settings of partition/drive. The settings can be opened via the Hardware Tab which gets displayed when properties are of the drive are accessed
MDF Files Used by other Processes:
There may be antivirus or backup routines scheduled on the system. SQL Server starts a particular database when it is accessed by users. In case when MDF or LDF file is already in use by an anti-virus scan or backup schedule, then the operating system does not allow SQL Server to use the particular database file
Solution:
Exempt Database files from anti-virus scans or schedule scans and backups at a time when Database is not in use
Note: Process Explorer is an off-the-shelf tool that can be used to investigate which processes are actually using/locking particular file.

Limited privileges to Logon Account:
Some technical resources suggest that the problem may be due to limited privileges to log-on account
Solution
Grant privileges to the Logon account so that it is able to access LOG folder of MS SQL Server

SQL Server Trace Files Consuming Lot of Space

Recently we faced a situation in which Hard Drive of one of our server machines was getting filled up. Investigation resulted in the discovery that a large number of trace files were being created
Initial study developed an opinion that it was due to default trace, however a formal study resulted in the finding which have been listed in the form of following Q/A.
Note: Information Applies to SQL Server 2005 and later versions

WHY DEFAULT TRACING IS DONE?
Supply auditing and performance data to reports available through the Management Studio Object Browser. E.g. Schema change history report [1]
HOW CRITICAL IT IS?
It depends upon the scenario. The best approach is to see the information being recorded in the trace to determine whether the information is suitable for you or not [2]
HOW RESOURCE GREEDY IT IS?
“The default trace is composed of five 20 megabyte trace files that are accessed in a round robin fashion, When the first file is full, the trace moves to the next file in the sequence. When the fifth file is full, the first file is deleted and a new file is created” [1]
In case a lot of space is consumed and a lot of files are being created then there is a possibility that other traces are running as well.
HOW TO FIND OUT WHAT TRACES ARE RUNNING?
The following command can be used [3]
SELECT * FROM fn_trace_getinfo(default);
GO
HOW TO STOP A TRACE?
Information regarding stopping a trace can be found in [2],[4],[5].



REFERENCES
[1] Audit SQL Server with the Default Trace, July 2007, Online: http://www.sqlconsulting.com/news1007.htm, Accessed: 07 June 2011
[2] Kadlec, J. Default Trace in SQL Server 2005, MSSQL Tips, November 2006, Online: http://www.mssqltips.com/tip.asp?tip=1111, Accessed: 07 June 2011
[3] fn_trace_getinfo (Transact-SQL), MSDN, http://msdn.microsoft.com/en-us/library/ms173875.aspx Accessed: 07 June 2011
[4] sp_trace_setstatus (Transact-SQL), MSDN, http://msdn.microsoft.com/en-us/library/ms176034.aspx, Accessed: 07 June 2011