Entities and Attributes - Revision history

Bacchas: /* What is an Entity Relationship Model */

2017-09-12T14:40:54Z

What is an Entity Relationship Model

← Older revision		Revision as of 14:40, 12 September 2017
Line 44:		Line 44:

	==What is an Entity Relationship Model==		==What is an Entity Relationship Model==
	~~===~~ A list of entities and attributes as well as all relationships between the entities that are of importance~~===~~		# A list of entities and attributes as well as all relationships between the entities that are of importance
	~~===~~ Provided background information such as entity descriptions, data types, and constraints~~===~~		# Provided background information such as entity descriptions, data types, and constraints
	~~===~~ NOTE: The model does not require a diagram, but the diagram is typically a very useful tool~~===~~		# NOTE: The model does not require a diagram, but the diagram is typically a very useful tool

	==Goals of ER Modeling ==		==Goals of ER Modeling ==
	*Capture all required information		*Capture all required information

Bacchas: /* SQL Command Types */

2017-09-12T14:40:24Z

SQL Command Types

← Older revision		Revision as of 14:40, 12 September 2017
Line 70:		Line 70:

	==SQL Command Types==		==SQL Command Types==
	*Query		;Query
	**SELECT(Retrieve the data values)		:SELECT(Retrieve the data values)
	*Data manipulation language (DML)		;Data manipulation language (DML)
	**INSERT, UPDATE, DELETE (Create or modify data values)		:INSERT, UPDATE, DELETE (Create or modify data values)
	* Data definition language (DDL)		;Data definition language (DDL)
	**CREATE, ALTER, DROP (define data structures)		:CREATE, ALTER, DROP (define data structures)
	* Transaction control (TC)		; Transaction control (TC)
	** COMMIT, ROLLBACK (Save or undo data value modifications)		: COMMIT, ROLLBACK (Save or undo data value modifications)
	*Data control language(DCL)		;Data control language(DCL)
	** GRANT, REVOKE (Set permissions to access database structures)		: GRANT, REVOKE (Set permissions to access database structures)

	----		----

	[[#Entity Defined\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Entity Defined\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas: /* Implementation-Free Models */

2017-09-12T14:39:33Z

Implementation-Free Models

← Older revision		Revision as of 14:39, 12 September 2017
Line 40:		Line 40:

	== Implementation-Free Models==		== Implementation-Free Models==
	~~===~~A good conceptual data model stays the same regardless of the type of database the system is eventually built or implanted on, This is what is meant by saying the model is implementation-free~~===~~		*A good conceptual data model stays the same regardless of the type of database the system is eventually built or implanted on, This is what is meant by saying the model is implementation-free
	*The data model should stay the same even if a database is not used at all		*The data model should stay the same even if a database is not used at all

	==What is an Entity Relationship Model==		==What is an Entity Relationship Model==
	=== A list of entities and attributes as well as all relationships between the entities that are of importance===		=== A list of entities and attributes as well as all relationships between the entities that are of importance===

Bacchas: /* Entity Defined */

2017-09-12T00:53:16Z

Entity Defined

← Older revision		Revision as of 00:53, 12 September 2017
Line 1:		Line 1:
	==Entity Defined==		==Entity Defined==
	~~===~~Something of significance to the business about which data must be known~~===~~		#Something of significance to the business about which data must be known
	~~===~~A name for a set of similar things that you can list~~===~~		#A name for a set of similar things that you can list
	~~===~~Usually a noun~~===~~		#Usually a noun
	~~===~~Examples: object, events, people~~===~~		#Examples: object, events, people
	~~===~~Entities have instances ~~===~~		#Entities have instances
	~~===~~An instance is a single occurrence of an entity ~~===~~		#An instance is a single occurrence of an entity
	[https://www.dropbox.com/s/lv1njvxls0lryth/entities.jpg?dl=0 Entities Table]		[https://www.dropbox.com/s/lv1njvxls0lryth/entities.jpg?dl=0 Entities Table]

Line 15:		Line 15:
	** Intangible, like SKILL LEVEL		** Intangible, like SKILL LEVEL
	** An Event, like CONCERT		** An Event, like CONCERT

	==WHAT IS AND ATTRIBUTE==		==WHAT IS AND ATTRIBUTE==
	* An attribute represents something of significance to the business		* An attribute represents something of significance to the business

Bacchas: /* Levels of Normalization */

2017-09-12T00:52:17Z

Levels of Normalization

← Older revision		Revision as of 00:52, 12 September 2017
Line 62:		Line 62:

	== Levels of Normalization==		== Levels of Normalization==
	~~===~~ 1NF: Eliminate all repeating values and identify a primary key or primary composite key.~~===~~		# 1NF: Eliminate all repeating values and identify a primary key or primary composite key.
	~~===~~2NF: Make certain the table is in 1NF and eliminate any partial dependencies.~~===~~		# 2NF: Make certain the table is in 1NF and eliminate any partial dependencies.
	~~===~~ 3NF: Make certain the table is in 2NF and remove any transitive dependencies.~~===~~		# 3NF: Make certain the table is in 2NF and remove any transitive dependencies.
	'''A many-to-many relationship can’t exist in a relational database. The most common approach to eliminating a many-to-many relationship is to create two one-to-many relationships by adding a bridging entity. A bridging entity is placed between the original entities and serves as a “filter” for the data.'''		'''A many-to-many relationship can’t exist in a relational database. The most common approach to eliminating a many-to-many relationship is to create two one-to-many relationships by adding a bridging entity. A bridging entity is placed between the original entities and serves as a “filter” for the data.'''

	==SQL Command Types==		==SQL Command Types==
	*Query		*Query

Bacchas: /* Database Normalization */

2017-09-12T00:51:32Z

Database Normalization

← Older revision		Revision as of 00:51, 12 September 2017
Line 54:		Line 54:
	#Many people unfamiliar with database design principles often ask, “Why not just put all the data in one big table?” This single-table approach leads to problems of data redundancy (duplication) and data anomalies (data inconsistencies).		#Many people unfamiliar with database design principles often ask, “Why not just put all the data in one big table?” This single-table approach leads to problems of data redundancy (duplication) and data anomalies (data inconsistencies).
	#To avoid these data issues, database normalization is used to create a design that reduces or eliminates data redundancy and, therefore, avoids data anomalies. In general, normalization helps database designers determine which attributes, or fields, belong to each entity. In turn, this information helps determine which fields belong in each table. Normalization is a multistage process that enables designers to take the raw data to be collected about an entity and develop the data into a structured, normalized form that reduces the risks associated with data redundancy. Data redundancy poses a special problem in databases because storing the same data in different places can cause problems when updates or changes to data are required.		#To avoid these data issues, database normalization is used to create a design that reduces or eliminates data redundancy and, therefore, avoids data anomalies. In general, normalization helps database designers determine which attributes, or fields, belong to each entity. In turn, this information helps determine which fields belong in each table. Normalization is a multistage process that enables designers to take the raw data to be collected about an entity and develop the data into a structured, normalized form that reduces the risks associated with data redundancy. Data redundancy poses a special problem in databases because storing the same data in different places can cause problems when updates or changes to data are required.
	:When a record contains repeating groups (that is, multiple entries for a single column), it’s considered unnormalized. First-normal form (1NF) indicates that all values of the columns are atomic—meaning they contain no repeating values. To convert records to 1NF, remove the repeating values by making each author entry a separate record,		#When a record contains repeating groups (that is, multiple entries for a single column), it’s considered unnormalized. First-normal form (1NF) indicates that all values of the columns are atomic—meaning they contain no repeating values. To convert records to 1NF, remove the repeating values by making each author entry a separate record,
	:When more than one field is used as the primary key for a table, the combination of fields is usually referred to as a composite primary key		#When more than one field is used as the primary key for a table, the combination of fields is usually referred to as a composite primary key
	'''Now that the repeating values have been eliminated and the records can be identified uniquely, the data is in 1NF'''		'''Now that the repeating values have been eliminated and the records can be identified uniquely, the data is in 1NF'''
	;A problem known as partial dependency can occur when the primary key consists of more than one field. Partial dependency means the fields contained in a record (row) depend on only one portion of the primary key		;A problem known as partial dependency can occur when the primary key consists of more than one field. Partial dependency means the fields contained in a record (row) depend on only one portion of the primary key

Bacchas: /* Database Normalization */

2017-09-12T00:51:01Z

Database Normalization

← Older revision		Revision as of 00:51, 12 September 2017
Line 52:		Line 52:
	==Database Normalization==		==Database Normalization==
	'''Storing data in a normalized format means only one update is required to reflect the new address, and it should always be the one that appears whenever your mailing address is needed.'''		'''Storing data in a normalized format means only one update is required to reflect the new address, and it should always be the one that appears whenever your mailing address is needed.'''
	:Many people unfamiliar with database design principles often ask, “Why not just put all the data in one big table?” This single-table approach leads to problems of data redundancy (duplication) and data anomalies (data inconsistencies).		#Many people unfamiliar with database design principles often ask, “Why not just put all the data in one big table?” This single-table approach leads to problems of data redundancy (duplication) and data anomalies (data inconsistencies).
	:To avoid these data issues, database normalization is used to create a design that reduces or eliminates data redundancy and, therefore, avoids data anomalies. In general, normalization helps database designers determine which attributes, or fields, belong to each entity. In turn, this information helps determine which fields belong in each table. Normalization is a multistage process that enables designers to take the raw data to be collected about an entity and develop the data into a structured, normalized form that reduces the risks associated with data redundancy. Data redundancy poses a special problem in databases because storing the same data in different places can cause problems when updates or changes to data are required.		#To avoid these data issues, database normalization is used to create a design that reduces or eliminates data redundancy and, therefore, avoids data anomalies. In general, normalization helps database designers determine which attributes, or fields, belong to each entity. In turn, this information helps determine which fields belong in each table. Normalization is a multistage process that enables designers to take the raw data to be collected about an entity and develop the data into a structured, normalized form that reduces the risks associated with data redundancy. Data redundancy poses a special problem in databases because storing the same data in different places can cause problems when updates or changes to data are required.
	:When a record contains repeating groups (that is, multiple entries for a single column), it’s considered unnormalized. First-normal form (1NF) indicates that all values of the columns are atomic—meaning they contain no repeating values. To convert records to 1NF, remove the repeating values by making each author entry a separate record,		:When a record contains repeating groups (that is, multiple entries for a single column), it’s considered unnormalized. First-normal form (1NF) indicates that all values of the columns are atomic—meaning they contain no repeating values. To convert records to 1NF, remove the repeating values by making each author entry a separate record,
	:When more than one field is used as the primary key for a table, the combination of fields is usually referred to as a composite primary key		:When more than one field is used as the primary key for a table, the combination of fields is usually referred to as a composite primary key

Bacchas: /* Database Normalization */

2017-09-12T00:50:21Z

Database Normalization

← Older revision		Revision as of 00:50, 12 September 2017
Line 52:		Line 52:
	==Database Normalization==		==Database Normalization==
	'''Storing data in a normalized format means only one update is required to reflect the new address, and it should always be the one that appears whenever your mailing address is needed.'''		'''Storing data in a normalized format means only one update is required to reflect the new address, and it should always be the one that appears whenever your mailing address is needed.'''
	~~===~~Many people unfamiliar with database design principles often ask, “Why not just put all the data in one big table?” This single-table approach leads to problems of data redundancy (duplication) and data anomalies (data inconsistencies).~~===~~		:Many people unfamiliar with database design principles often ask, “Why not just put all the data in one big table?” This single-table approach leads to problems of data redundancy (duplication) and data anomalies (data inconsistencies).
	~~===~~To avoid these data issues, database normalization is used to create a design that reduces or eliminates data redundancy and, therefore, avoids data anomalies. In general, normalization helps database designers determine which attributes, or fields, belong to each entity. In turn, this information helps determine which fields belong in each table. Normalization is a multistage process that enables designers to take the raw data to be collected about an entity and develop the data into a structured, normalized form that reduces the risks associated with data redundancy. Data redundancy poses a special problem in databases because storing the same data in different places can cause problems when updates or changes to data are required.~~===~~		:To avoid these data issues, database normalization is used to create a design that reduces or eliminates data redundancy and, therefore, avoids data anomalies. In general, normalization helps database designers determine which attributes, or fields, belong to each entity. In turn, this information helps determine which fields belong in each table. Normalization is a multistage process that enables designers to take the raw data to be collected about an entity and develop the data into a structured, normalized form that reduces the risks associated with data redundancy. Data redundancy poses a special problem in databases because storing the same data in different places can cause problems when updates or changes to data are required.
	~~===~~When a record contains repeating groups (that is, multiple entries for a single column), it’s considered unnormalized. First-normal form (1NF) indicates that all values of the columns are atomic—meaning they contain no repeating values. To convert records to 1NF, remove the repeating values by making each author entry a separate record,~~===~~		:When a record contains repeating groups (that is, multiple entries for a single column), it’s considered unnormalized. First-normal form (1NF) indicates that all values of the columns are atomic—meaning they contain no repeating values. To convert records to 1NF, remove the repeating values by making each author entry a separate record,
	~~===~~When more than one field is used as the primary key for a table, the combination of fields is usually referred to as a composite primary key~~===~~		:When more than one field is used as the primary key for a table, the combination of fields is usually referred to as a composite primary key
	'''Now that the repeating values have been eliminated and the records can be identified uniquely, the data is in 1NF'''		'''Now that the repeating values have been eliminated and the records can be identified uniquely, the data is in 1NF'''
	~~===~~A problem known as partial dependency can occur when the primary key consists of more than one field. Partial dependency means the fields contained in a record (row) depend on only one portion of the primary key~~===~~		;A problem known as partial dependency can occur when the primary key consists of more than one field. Partial dependency means the fields contained in a record (row) depend on only one portion of the primary key
	'''By removing Partial dependency your table is now second-normal-form (2NF)'''		'''By removing Partial dependency your table is now second-normal-form (2NF)'''
	~~===~~A transitive dependency means at least one value in the record isn’t dependent on the primary key but on another field in the record. In this case, the contact person from the publisher’s office is actually dependent on the publisher, not on the book.~~===~~		:A transitive dependency means at least one value in the record isn’t dependent on the primary key but on another field in the record. In this case, the contact person from the publisher’s office is actually dependent on the publisher, not on the book.

	== Levels of Normalization==		== Levels of Normalization==
	=== 1NF: Eliminate all repeating values and identify a primary key or primary composite key.===		=== 1NF: Eliminate all repeating values and identify a primary key or primary composite key.===

Bacchas: /* ERD Entity relationship diagram */

2017-09-12T00:42:56Z

ERD Entity relationship diagram

← Older revision		Revision as of 00:42, 12 September 2017
Line 36:		Line 36:

	==ERD Entity relationship diagram==		==ERD Entity relationship diagram==
	~~===~~An entity relationship diagram (ERD) is a consistent tool that can be used to represent the data requirements of a business regardless of type of database that is used, and even in the absence of one~~===~~		:An entity relationship diagram (ERD) is a consistent tool that can be used to represent the data requirements of a business regardless of type of database that is used, and even in the absence of one

	== Implementation-Free Models==		== Implementation-Free Models==
	===A good conceptual data model stays the same regardless of the type of database the system is eventually built or implanted on, This is what is meant by saying the model is implementation-free===		===A good conceptual data model stays the same regardless of the type of database the system is eventually built or implanted on, This is what is meant by saying the model is implementation-free===

Bacchas at 00:40, 12 September 2017

2017-09-12T00:40:11Z

← Older revision		Revision as of 00:40, 12 September 2017
Line 29:		Line 29:
	: Association between 2 or more entities		: Association between 2 or more entities

	*Conceptual model: A data model, usually represented by an entity relationship diagram (ERD)		;Conceptual model: A data model, usually represented by an entity relationship diagram (ERD)
	** The client dream would be the conceptual model		: The client dream would be the conceptual model
	*Physical model: A design for an object (a car, a house, a database, etc) which includes implementation details such as size, volume, weight, etc)		;Physical model: A design for an object (a car, a house, a database, etc) which includes implementation details such as size, volume, weight, etc)
	** The physical reality will become the physical model		: The physical reality will become the physical model
	*All many-to-many relationships must be reduced to a set of on-t--many relationships		;All many-to-many relationships must be reduced to a set of on-t--many relationships

	==ERD Entity relationship diagram==		==ERD Entity relationship diagram==