Suppliers and Parts database

Last updated

The Suppliers and Parts database is an example relational database that is referred to extensively in the literature[ citation needed ] and described in detail in C. J. Date's An Introduction to Database Systems, 8th ed. [1] It is a simple database comprising three tables: Supplier, Part and Shipment, and is often used as a minimal exemplar of the interrelationships found in a database.

  1. The Supplier relation [2] holds information about suppliers. The SID attribute identifies the supplier, while the other attributes each hold one piece of information about the supplier.
  2. The Part relation holds information about parts. Likewise, the PID attribute identifies the part, while the other attributes hold information about the part.
  3. The Shipment relation holds information about shipments. The SID and PID attributes identify the supplier of the shipment and the part shipped, respectively. The remaining attribute indicates how many parts where shipped.
  • Referential constraints known as Foreign keys ensure that these attributes can only hold values that are also found in the corresponding attributes in the Supplier and Parts relations.
  • It is assumed that only one shipment exists for each supplier/part pairing, which isn't realistic for real world scenarios. This is intentionally oversimplified for pedagogical purposes, as is the entire database.

SQL

The following SQL schema is one possible expression of the Suppliers-and-Parts database.

CREATETABLESupplier(SIDintprimarykey,SNamevarchar(10)NOTNULL,StatusintNOTNULL,Cityvarchar(10)NOTNULL)CREATETABLEPart(PIDintprimarykey,PNamevarchar(10)NOTNULL,ColorintNOTNULL,WeightrealNOTNULL,Cityvarchar(10)NOTNULL)CREATETABLEShipment(SIDintNOTNULLFOREIGNKEYREFERENCESSupplier(SID),PIDintNOTNULLFOREIGNKEYREFERENCESPart(PID),QtyintNOTNULL,PRIMARYKEY(SID,PID))

Notes:

  1. The ID attributes are simple integers, but they could be (among other things) UUIDs or a system-defined identifier type that holds system-generated values.
  2. The choice of VARCHAR(10) is arbitrary and would be too small for real-world use.
  3. The application of the NOT NULL constraint to all attributes is a design decision based on the view that NULLs are to be avoided. It is not, strictly speaking, a requirement of the schema.

Related Research Articles

Database normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd as part of his relational model.

A relational database is a digital database based on the relational model of data, as proposed by E. F. Codd in 1970. A software system used to maintain relational databases is a relational database management system (RDBMS). Many relational database systems have an option of using the SQL for querying and maintaining the database.

The relational model (RM) for database management is an approach to managing data using a structure and language consistent with first-order predicate logic, first described in 1969 by English computer scientist Edgar F. Codd, where all data is represented in terms of tuples, grouped into relations. A database organized in terms of the relational model is a relational database.

SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables.

Object-relational database database management system

An object-relational database (ORD), or object-relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. In addition, just as with pure relational systems, it supports extension of the data model with custom data types and methods.

First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain. The first definition of the term, in a 1971 conference paper by Edgar Codd, defined a relation to be in first normal form when none of its domains have any sets as elements.

In the relational model of databases, a primary key is a specific choice of a minimal set of attributes (columns) that uniquely specify a tuple (row) in a relation (table). Informally, a primary key is "which attributes identify a record", and in simple cases are simply a single attribute: a unique id. More formally, a primary key is a choice of candidate key ; any other candidate key is an alternate key.

In the context of relational databases, a foreign key is a set of attributes subject to a certain kind of inclusion dependency constraint, specifically a constraint that the tuples consisting of the foreign key attributes in one relation, R, must also exist in some other relation, S, and furthermore that those attributes must also be a candidate key in S. In simpler words, a foreign key is a set of attributes that references a candidate key. For example, a table called TEAM may have an attribute, MEMBER_NAME, which is a foreign key referencing a candidate key, PERSON_NAME, in the PERSON table. Since MEMBER_NAME is a foreign key, any value existing as the name of a member in TEAM must also exist as a person's name in the PERSON table; in other words, every member of a TEAM is also a PERSON.

Referential integrity

Referential integrity is a property of data stating that all its references are valid. In the context of relational databases, it requires that if a value of one attribute (column) of a relation (table) references a value of another attribute, then the referenced value must exist.

In the context of SQL, data definition or data description language (DDL) is a syntax similar to a computer programming language for defining data structures, especially database schemas. DDL statements create and modify database objects such as tables, indexes, and users. Common DDL statements are CREATE, ALTER, and DROP.

A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data, unlike a natural key which is derived from application data.

An SQL join clause - corresponding to a join operation in relational algebra - combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table can JOIN to itself in a self-join.

A table is a collection of related data held in a table format within a database. It consists of columns and rows.

Null (SQL) special marker and keyword in SQL

Null is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. In SQL, NULL is a reserved word used to identify this marker.

Entity–attribute–value model (EAV) is a data model to encode, in a space-efficient manner, entities where the number of attributes that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest. Such entities correspond to the mathematical notion of a sparse matrix.

In database relational modeling and implementation, a unique key is a set of attributes (columns) within a relational database table, such that:

  1. the table does not have two distinct rows or records with the same values for these columns;
  2. this set of columns is minimal; i.e., removing any column from the key would result in duplicate values in the resulting subset.
Associative entity

An associative entity is a term used in relational and entity–relationship theory. A relational database requires the implementation of a base relation to resolve many-to-many relationships. A base relation representing this kind of entity is called, informally, an associative table.

Entity Framework (EF) is an open source object-relational mapping (ORM) framework for ADO.NET. It was a part of .NET Framework, but since Entity Framework version 6 it is separated from .NET framework.

Relation (database) concept in relational database theory

In relational database theory, a relation, as originally defined by E. F. Codd, is a set of tuples (d1, d2, ..., dn), where each element dj is a member of Dj, a data domain. Codd's original definition notwithstanding, and contrary to the usual definition in mathematics, there is no ordering to the elements of the tuples of a relation. Instead, each element is termed an attribute value. An attribute is a name paired with a domain. An attribute value is an attribute name paired with an element of that attribute's domain, and a tuple is a set of attribute values in which no two distinct elements have the same name. Thus, in some accounts, a tuple is described as a function, mapping names to values.

The SQL:1999 standard introduced a number of object-relational database features into SQL, chiefly among them structured user-defined types, usually called just structured types. These can be defined either in plain SQL with CREATE TYPE but also in Java via SQL/JRT. SQL structured types allow single inheritance.

References

  1. ↑ Date, C.J. (2004). "Chapter 3 / An Introduction to Relational Databases". An Introduction to Database Systems — Eighth Edition. Pearson Education Inc. ISBN   0-321-18956-6.
  2. ↑ Relations and SQL tables are roughly synonymous.
This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.