loading..
Русский    English
23:34

Database «Painting»

Database schema consists of 3 tables:

  1. utQ (Q_ID int, Q_NAME varchar(35));
  2. utV (V_ID int, V_NAME varchar(35), V_COLOR char(1));
  3. utB (B_Q_ID int, B_V_ID int, B_VOL tinyint, B_DATETIME datetime).

The table utQ includes square identifier, square name. Note that non-painted square is black.

The table utV includes balloon identifier, balloon name, and paint color.

The table utB shows information on painting square with paint balloon and includes the square identifier, the balloon identifier, the paint quantity, and time of painting.

It should be noted that

  • Balloon may be of one from three colors: red (V_COLOR='R'), green (V_COLOR='G'), or blue (V_COLOR='B');
  • Any balloon is originally full with volume of 255;
  • square color is defined in accordance with RGB rule, i.e. R=0, G=0, B=0 is black, whereas R=255, G=255, B=255 is white;
  • any record in the table utB decreases paint quantity in the balloon by B_VOL and increase paint quantity in the square by the same value;
  • B_VOL must be more than 0 and less or equal 255;
  • paint quantity of the same color in one square may not be over 255;
  • paint quantity in a balloon may not be less than 0;
  • time of painting (B_DATETIME) is given to within a second, i.e. it does not contain milliseconds.

База данных








Fig. Painting database schema

Some explanations to the scheme.

Identifiers of black squares are absent from utB table. It due to the fact that B_VOL does not allow NULL values and is strictly greater than zero. Thus, each record in utB table describes the fact of painting a square by a can (balloon), the black square (R=0, G=0, B=0) was not painted at all.

Constraints of the scheme allow simultaneous painting one square by several cans, as well as simultaneous painting the several squares with aid of one can. However these forbid painting one square by the same ballon at a time.

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100