The prerequisites of the lab are:
2. Visual Basic 6.0
In relational database systems (DBS) data are represented using tables (relations). A query issued against the DBS also results in a table. A table is uniquely identified by its name and consists of rows that contain the stored information, each row containing exactly one tuple (or record). A table can have one or more columns. A column is made up of a column name and a data type, and it describes an attribute of the tuples. The structure of a table, also called relation schema, thus is defined by its attributes. The type of information to be stored in a table is defined by the data types of the attributes at table creation time. SQL uses the terms table, row, and column for relation, tuple, and attribute, respectively. A table can have up to 254 columns which may have different or same data types and sets of values (domains), respectively. Possible domains are alphanumeric data (strings), numbers and date formats.
The SQL language has several parts:
- Data-definition Language (DDL): the SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas.
- Interactive data-manipulation language (DML): the SQL DML includes a query language based on both the relational algebra and the tuple relational calculus. It includes also commands to insert tuples into, delete tuples from, and modify tuples in the database.
- View definition: the SQL DDL includes commands for defining views
- Transaction control: SQL includes commands for specifying the beginning and ending of transactions.
- Embedded SQL and Dynamic SQL: embedded and dynamic SQL define how SQL statements can be embedded within general-purpose programming languages such as C, C++, Java etc.,
- Integrity: the SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed.
- Authorization: the SQL DDL includes commands for specifying access rights to relations and views.
The SQL data definition language allows specification of not only a set of relations but also information about each relation, including
- The schema for each relation.
- The domain of values associated with each attribute.
- Integrity constraints
- The set of indices to be maintained for each relation.
- Security and authorization information for each relation.
- The physical storage structure of each relation on disk.
Domain types in SQL:
The SQL standard supports a variety of built-in domain types such as,
- char(n). Fixed length character string, with user-specified length
- varchar(n). Variable length character strings, with user-specified maximum length
- Integer (a finite subset of the integers that is machine-dependent).
- Small integer (a machine-dependent subset of the integer domain type).
- numeric(p,d). Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point.
- real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision.
- float(n). Floating point number, with user-specified precision of at least n
- date: A calendar date containing a (four digit) year, month and day of the month.
- time: the time of day in hours, minutes and seconds.
- timestamp: a combination of date and time.