5.6 ERDs and Query

Question 1

A relational database is a collection of interrelated two-dimensional tables, consisting of rows and columns. Each row represents a record, and each column (or field) represents an attribute (or characteristic) of that record.

Answer 1

word

Question 2

Every record in the database must contain at least one field that uniquely identifies that record so that it can be retrieved, updated, and sorted.

Answer 2

worddd

Question 3

what is the identifier field called

Answer 3

primary key

Question 4

when can related tables (Erds) be joined

Answer 4

when they contain common columns

Question 5

how do you find info in a database?

Answer 5

use a query language (SQL or QBE)S

Question 6

SQL example

Answer 6

Typical key words are SELECT (to choose a desired attribute), FROM (to specify the table or tables to be used), and WHERE (to specify conditions to apply in the query).

Question 7

How does QBE work

Answer 7

user fills out a form to construct a samplle of data desired!! then use drag and drop

Question 8

how to make erd

Answer 8

1) identify the business rules (how does the business operate? what is the info)
2) label the PK
3) find relevant attributes
4) connect to other tables with apporporiate connectivity and cardinality

Question 9

how to find different attributes of tables

Answer 9

use data dictionary

Question 10

unary relationship

Answer 10

associaiton with one entityy

Question 11

binary relationshoip

Answer 11

relationship where two entities are associated

Question 12

ternary relationship

Answer 12

exist when three entities are assocaited

Question 13

cardinality

Answer 13

max number of time one entity can be associated with another

Question 14

cardinality ex: mandatory single

Answer 14

two straight lines

Question 15

cardinality ex: optional single

Answer 15

circle straight line

Question 16

cardinality ex: mandatory many

Answer 16

staight line then 3 way fork

Question 17

cardinality ex: optional manu

Answer 17

circle than many

Question 18

how to figure out appropraite cardinality

Answer 18

use logic!! can one student have many phone numbers, can one phone number have many students

Question 19

joint table

Answer 19

result of a many ot many relationship!!!

put a mandatory single on the end connected to erd, and optional many connected to the end associated with joint table

Question 20

normalization

Answer 20

method for analyzing and reducing a relational database to the most streamlined form to reduce REDUNDANCY, increase integrity, and optimize processing performance!!

Question 21

Data normalization is a methodology for organizing attributes into tables so that redundancy among the non-key attributes is eliminated. The result of the data normalization process is a properly structured relational database.

Answer 21

this!!

Question 22

Data normalization requires a list of all the attributes that must be incorporated into the database and a list of all of the defining associations, or functional dependencies, among the attributes. Functional dependencies are a means of expressing that the value of one particular attribute is associated with a specific single value of another attribute. For example, for a Student Number 05345 at a university, there is exactly one Student Name, John C. Jones, associated with it. That is, Student Number is referred to as the determinant because its value determines the value of the other attribute. We can also say that Student Name is functionally dependent on Student Number.

Answer 22

Data normalization requires a list of all the attributes that must be incorporated into the database and a list of all of the defining associations, or functional dependencies, among the attributes. Functional dependencies are a means of expressing that the value of one particular attribute is associated with a specific single value of another attribute. For example, for a Student Number 05345 at a university, there is exactly one Student Name, John C. Jones, associated with it. That is, Student Number is referred to as the determinant because its value determines the value of the other attribute. We can also say that Student Name is functionally dependent on Student Number.

Question 23

first normal form

Answer 23

-attributes under consideration are listed in one table, and PKs are established
-contians repeating groups and describes multiple entitites
-data redundancy
-lack of data integrity
-uses a flat file
-REPEATING groups!

Question 24

second normal form

Answer 24

• break first normal form into smaller tables to reduce data redundancy
  -does not allow partial functional dependnacies
  -EVERY non key attribute must be functionally dependant on the entire pk of the table

Question 25

third normal form

Answer 25

-non key attributes are not allowed to define other non key attributes - one non key attribute is funcitonally depenedant on another -no data redundancy! -all FK appear whwere needed to link related tables -> thse tables use the join operation!!! combining records for two or more tables in a database to obtain information that is locaated in different tbales.

Question 26

steps of normalization

Answer 26

1) determine funcitonal dependices among attributes 2) create first normla form 3) to second normal form 4) to third normal orm

Question 27

The join operation combines two or more tables in a database to obtain information that is located in different tables. when does the join process happen

Answer 27

after the third normal form is created

Question 28

At the end of this join process, we have a complete ORDER. Normalization is beneficial when maintaining databases over a period of time. One example is the likelihood of having to change the price of each pizza. If the pizza shop increases the price of the Meat Feast from $12.00 to $12.50, this process is one easy step in Figure 5.18. The price field is changed to $12.50 and the ORDER is automatically updated with the current value of the price.

Answer 28

At the end of this join process, we have a complete ORDER. Normalization is beneficial when maintaining databases over a period of time. One example is the likelihood of having to change the price of each pizza. If the pizza shop increases the price of the Meat Feast from $12.00 to $12.50, this process is one easy step in Figure 5.18. The price field is changed to $12.50 and the ORDER is automatically updated with the current value of the price.

Question 29

summary 1

Answer 29

1. Discuss ways that common challenges in managing data can be addressed using data governance.   The following are three common challenges in managing data: Data are scattered throughout organizations and are collected by many individuals using various methods and devices. These data are frequently stored in numerous servers and locations and in different computing systems, databases, formats, and human and computer languages. Data come from multiple sources. Information systems that support particular business processes impose unique requirements on data, which results in repetition and conflicts across an organization. One strategy for implementing data governance is master data management. Master data management provides companies with the ability to store, maintain, exchange, and synchronize a consistent, accurate, and timely “single version of the truth” for the company’s core master data. Master data management manages data gathered from across an organization, manages data from multiple sources, and manages data across business processes within an organization.

Question 30

summary 2

Answer 30

2. Discuss the advantages and disadvantages of relational databases.   Relational databases enable people to compare information quickly by row or column. Users also can easily retrieve items by finding the point of intersection of a particular row and column. However, large-scale relational databases can be composed of numerous interrelated tables, making the overall design complex, with slow search and access times.

Question 31

summary 3

Answer 31

3. Define Big Data and explain its basic characteristics.   Big Data is composed of high-volume, high-velocity, and high-variety information assets that require new forms of processing in order to enhance decision making, lead to insights, and optimize business processes. Big Data has three distinct characteristics that distinguish it from traditional data: volume, velocity, and variety. Volume: Big Data consists of vast quantities of data. Velocity: Big Data flows into an organization at incredible speeds. Variety: Big Data includes diverse data in differing formats.

Question 32

summary 4

Answer 32

4. Explain the elements necessary to successfully implement and maintain data warehouses.   To successfully implement and maintain a data warehouse, an organization must: Link source systems that provide data to the warehouse or mart. Prepare the necessary data for the data warehouse using data integration technology and processes. Decide on an appropriate architecture for storing data in the data warehouse or data mart. Select the tools and applications for the variety of organizational users. Establish appropriate metadata, data quality, and governance processes to ensure that the data warehouse or mart meets its purposes.

Question 33

summary 5

Answer 33

5. Describe the benefits and challenges of implementing knowledge management systems in organizations.   Organizations can realize many benefits with KMSs, including: Best practices readily available to a wide range of employees. Improved customer service. More efficient product development. Improved employee morale and retention. Challenges to implementing KMSs include: Employees must be willing to share their personal tacit knowledge. Organizations must create a knowledge management culture that rewards employees who add their expertise to the knowledge base. The knowledge base must be continually maintained and updated. Companies must be willing to invest in the resources needed to carry out these operation

Question 34

summary

Answer 34

6. Understand the processes of querying a relational database, entity-relationship modelling, and normalization and joins.   The most commonly performed database operation is requesting information. Structured query language is the most popular query language used for this operation. SQL allows people to perform complicated searches by using relatively simple statements or key words. Typical key words are SELECT (to specify a desired attribute), FROM (to specify the table to be used), and WHERE (to specify conditions to apply in the query). Another way to find information in a database is to use query by example. In QBE, the user fills out a grid or template—also known as a form—to construct a sample or a description of the data desired. Users can construct a query quickly and easily by using drag-and-drop features in a DBMS such as Microsoft Access. Conducting queries in this manner is simpler than keying in SQL commands. Designers plan and create databases through the process of entity-relationship modelling, using an entity-relationship diagram. ER diagrams consist of entities, attributes, and relationships. Entities are pictured in boxes, and relationships are represented as diamonds. The attributes for each entity are listed, and the primary key is underlined. ER modelling is valuable because it allows database designers to communicate with users throughout the organization to ensure that all entities and the relationships among the entities are represented. This process underscores the importance of taking all users into account when designing organizational databases. Notice that all entities and relationships in our example are labelled in terms that users can understand. Normalization is a method for analyzing and reducing a relational database to its most streamlined form to ensure minimum redundancy, maximum data integrity, and optimal processing performance. When data are normalized, attributes in each table depend only on the primary key. The join operation combines records from two or more tables in a database to produce information that is located in different tables.