2.8.1.2 Tables
Before you can create a Table in Access, you must create a database container and give it a name with the extension. mdb. Database creation is simple process and is explained in detail with an example later in this chapter. Suffice it to say that a Table is made up of columns and rows. Columns are referred to as fields, which are attributes of an entity. Rows are referred to as records, also called tuples.
2.8.1.3 Queries
One of the main purposes of storing data in a database is that the data may be retrieved later as needed without having to write complex programs. This purpose is accomplished in Access and other databases by writing SQL statements. A group of such statements is called a query. It enables you to retrieve, update and display data in the Tables. You may display data from more than one Table by using a Join operation. In addition, you may insert or delete data in the Tables.
Access also provides a visual graphic user interface to create queries. This bypasses writing SQL statements and makes it appealing to beginning and not-so-savvy users, who can use wizards or a GUI interface to create queries. Queries can extract information in a variety of ways. You can make them as simple or as complex as you like. You may specify various criteria to get desired information or perform comparisons, or you may want to perform some calculations and obtain the results. In essence, operators, functions and expressions are the building blocks for Access operation.
2.8.2 SQL Server Databases
The Microsoft SQL Server Database Engine is a service for storing and processing data in either relational (tabular) format or as XML documents. Various tasks performed by the Database Engine include:
- Designing and creating a database to hold relational Tables or XML documents
- Accessing and modifying the data stored in the database
- Implementing Web sites and applications
- Building procedures
- Optimizing the performance of the database
The SQL Server database is a complex entity, made up of multiple components. It is more complex than MS Access database, which can be simply copied and distributed. Certain procedures have to be followed for copying and distributing a SQL server database.
SQL Server is used by a diverse group of professionals with diverse needs and requirements. To satisfy different needs, SQL Server comes in five editions: Enterprise edition, Standard edi-tion, Workgroup edition, Developer edition and Express edition. The most common editions are Enterprise, Standard and Workgroup. It is noteworthy that the database engine is virtually the same in all of these editions.
A SQL Server database can be stored on the disk using three types of files—primary data files, secondary data files and transaction log files. Primary data files are created first and contain user-defined objects like Tables and views and system objects. These files have the extension. mdf. If the database grows too big for a disk, it can be stored as secondary files with the extension. ndf. The SQL Server still treats these files as if they were together. The data file is made up of many objects. The transaction log files carry the. ldf extension. All transactions to the database are recorded in this file.
Figure 2.10 illustrates the structure of the SQL Server Database. Each Java application has to access the server, which in turn accesses the SQL database.
