Database Recycled materials company

The company receiving recycled and has several points of reception. Each point receives money to pay to suppliers for raw materials. In fact, the diagram shows two databases. In each task under this scheme uses only one pair of tables (with the suffix _o, or without it).

In tables Income_o and Outcome_o the primary key is a pair of attributes (point, date) - point number and date of reception. This key must simulate a situation when information about receiving money at the receiving point, and their extradition to suppliers recorded in the database no more than once a day.

Fig. 2.1.The scheme of Recycled materials company database.


The data values in the column date does not contain the time, for example, 2001-03-22 00:00:00.000. Unfortunately, the use for the datetime data type of column may cause confusion, since it is obvious that taking into account the time does not allow to limit multiple input values with the same date (and the item number) and different time of day. This deficiency associated with the lack of specific data types for date and time have been overcome in  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server 2008. To ensure the correct input in SQL Server 2000 the CK_Income_o restriction can be used:

  1. ALTER TABLE Income_o ADD
  3. (
  4. [point],
  5. [date]
  6. ),
  8. (
  9. DATEPART(hour,[date]) + DATEPART(minute,[date]) +
  10. DATEPART(second,[date]) + DATEPART(millisecond,[date]) = 0
  11. )

This restriction (the amount of hours, minutes, seconds and milliseconds is equal to zero) would not set any time other than 00:00:00.000. With such restriction the primary key will actually ensure that there is only one row per day for each point of reception.

The Income_o (point, date, inc) table contains information on the income (inc) per point of reception (point). Similar table Outcome_o (point, date, out) is used to control the expenses (out).

The second pair of tables Income (code, point, date, inc) and Outcome (code, point, date, out) simulates the situation where the income and expenses of money can be recorded several times a day. It should be noted that if the record in the last table only date without time (that occurs), then no natural combination of attributes can be used as the primary key, since the amount of money can be the same. Therefore, we must take into account the time, or add an artificial key. We used the second option, adding an integer column code only in order to ensure uniqueness of records in the table.

Bookmark and Share


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 CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.