Content deleted Content added
→See also: Datalog |
I introduced the acronym FK. |
||
| (11 intermediate revisions by 11 users not shown) | |||
Line 3:
{{more citations needed| date=January 2024| reason=some refs but tech article needs more}}
A '''relational database'''
==History==
The
# Present the data to the user as [[relation (database)|relation]]s (a presentation in tabular form, i.e. as a ''collection'' of [[table (database)|tables]] with each table consisting of a set of [[Row (database)|rows]] and [[Column (database)|columns]]);
Line 25:
|isbn=978-3540483977
|quote=The product was called SQL/DS (Structured Query Language/Data Store) and ran under the DOS/VSE operating system environment}}</ref>
The first system sold as an RDBMS was [[Multics Relational Data Store]] (June 1976).{{
The first systems that were relatively faithful implementations of the relational model were from:
* University of Michigan – [[MICRO Relational Database Management System|Micro DBMS]] (1969)<ref name=ST_1>{{cite journal| title=A set theoretic data structure and retrieval language| author1=Hershey, W.R.| author2=Easthope, C.H.| url=https://dl.acm.org/doi/10.1145/1095495.1095500| publisher=[[Association for Computing Machinery]]| journal=ACM SIGIR Forum| volume=7| issue=4| pages=
* Massachusetts Institute of Technology (1971)<ref name=SIG_1>{{cite book| title=SIGFIDET '74: Proceedings of the 1974 ACM SIGFIDET (Now SIGMOD) Workshop on Data Description, Access and Control: Data Models: Data-Structure-Set versus Relational| url=https://dl.acm.org/doi/book/10.1145/800297| publisher=[[Association for Computing Machinery]]| date=1 January 1975| doi=10.1145/800297| access-date=4 January 2024| isbn=978-1-4503-7418-7}}</ref>
* IBM UK Scientific Centre at Peterlee – [[IBM IS1|IS1]] (1970–72),<ref name=PS_1>{{cite book| title=The Peterlee IS/1 System| author=Notley, M.G.| url=https://scholar.google.com/scholar?lookup=0&q=NOTLEY,+M.G.+The+Peterlee+IS/1+System,+I.B.M.+(U.K.)+Scientific+Centre+Report,+March+1972.+UKSC-0018.&hl=en&as_sdt=0,5| publisher=IBM United Kingdom Scientific Centre| date=1972| access-date=4 January 2024}}</ref> and its successor, [[PRTV]] (1973–79).<ref name="Todd 1976">{{cite journal|title=The Peterlee Relational Test Vehicle - A System Overview|journal=IBM Systems Journal|year=1976|first=Stephen|last=Todd|volume=15|issue=4|pages=285–308|doi=10.1147/sj.154.0285}}</ref>
Line 52:
Part of this processing involves consistently being able to select or modify one and only one row in a table. Therefore, most physical implementations have a unique [[primary key]] (PK) for each row in a table. When a new row is written to the table, a new unique value for the primary key is generated; this is the key that the system uses primarily for accessing the table. System performance is optimized for PKs. Other, more [[natural key]]s may also be identified and defined as [[alternate key]]s (AK). Often several columns are needed to form an AK (this is one reason why a single integer column is usually made the PK). Both PKs and AKs have the ability to uniquely identify a row within a table. Additional technology may be applied to ensure a unique ID across the world, a [[globally unique identifier]], when there are broader system requirements.
The primary keys within a database are used to define the relationships among the tables. When a PK migrates to another table, it becomes a foreign key (FK) in the other table. When each cell can contain only one value and the PK migrates into a regular entity table, this design pattern can represent either a [[One-to-one (data model)|one-to-one]] or [[One-to-many (data model)|one-to-many]] relationship. Most relational database designs resolve [[Many-to-many (data model)|many-to-many]] relationships by creating an additional table that contains the PKs from both of the other entity tables{{snd}} the relationship becomes an entity; the resolution table is then named appropriately and the two FKs are combined to form a PK. The migration of PKs to other tables is the second major reason why system-assigned integers are used normally as PKs; there is usually neither efficiency nor clarity in migrating a bunch of other types of columns.
===Relationships===
Relationships are a logical connection between different tables (entities), established on the basis of interaction among these tables. These relationships can be modelled as an [[Entity–relationship model|entity-relationship model]].
==Transactions==
Line 122:
{{Main|Unique key|Primary key}}
Every [[Relation (database)|relation]]/table has a primary key, this being a consequence of a relation being a [[Set (mathematics)|set]].{{Sfnp|Date|1984|p=268}} A primary key uniquely specifies a tuple within a table. While natural attributes (attributes used to describe the data being entered) are sometimes good primary keys, [[surrogate key]]s are often used instead. A surrogate key is an artificial attribute assigned to an object which uniquely identifies it (for instance, in a table of information about students at a school they might all be assigned a student ID in order to differentiate them). The surrogate key has no intrinsic (inherent) meaning, but rather is useful through its ability to uniquely identify a tuple.
Another common occurrence, especially in regard to N:M cardinality is the [[Compound key|composite key]]. A composite key is a key made up of two or more attributes within a table that (together) uniquely identify a record.<ref>{{
===Foreign key===
Line 159:
[[File:RDBMS structure.png|thumbnail|right|The general structure of a relational database]]
{{See also|Database#Database management system}}
Connolly and Begg define database management system (DBMS) as a "software system that enables users to define, create, maintain and control access to the database".<ref name="Begg-p64">{{cite book |last1=Connolly |first1=Thomas M. |last2=Begg |first2=Carolyn E. |date=2014 |title=Database Systems – A Practical Approach to Design Implementation and Management |edition=6th |publisher=Pearson |isbn=978-1292061184 |page=64}}</ref> RDBMS is an extension of that
An alternative definition for a ''relational database management system'' is a database management system (DBMS) based on the [[relational model]]. Most databases in widespread use today are based on this model.<ref>{{cite book |last1=Pratt |first1=Philip J. |last2=Last |first2=Mary Z. |date=2014-09-08 |title=Concepts of Database Management |edition=8 |publisher=Course Technology |isbn=9781285427102 |page=29 }}</ref>
RDBMSs have been a common option for the storage of information in databases used for financial records, manufacturing and logistical information, personnel data, and other applications since the 1980s. Relational databases have often replaced legacy [[hierarchical database]]s and [[network database]]s, because RDBMS were easier to implement and administer. Nonetheless, relational stored data received continued, unsuccessful challenges by [[object database]] management systems in the 1980s and 1990s, (which were introduced in an attempt to address the so-called [[object–relational impedance mismatch]] between relational databases and object-oriented application programs), as well as by [[XML database]] management systems in the 1990s.<ref>{{
==Distributed relational databases==
Line 203:
==Sources==
*{{cite book |last=Date |first=C. J. |author-link=Christopher J. Date |date=1984 |title=A Guide to DB2 |edition=student |publisher=[[Addison-Wesley]] |isbn=0201113171 |ol=OL2838595M |oclc=256383726 |url=https://archive.org/details/guidetodb2users00date}}
{{Wikibooks |Structured Query Language}}
| |||