Download DAD 220 Study Set: Exams with Complete Solutions and more Exams Digital Systems Design in PDF only on Docsity!
DAD 220 NEW COMBINED STUDY SET EXAMS WITH COMPLETE SOLUTIONS 100% VERIFIED9 (DAD-220 Test Exam,DAD220 Ch 3,SNHU DAD 220,DAD 220 - 3-1 quiz & DAD 220 - Introduction to Course)
-DAD-220 Test Exam
-DAD220 Ch 3
-SNHU DAD 220
-DAD 220 - 3-1 quiz
-DAD 220 - Introduction to Course
DAD-220 Test Exam
Data
raw information, collected raw facts and values - stock prices
Types of data formats
text, image, audio, video
Types of data sources
industries, governments, individuals, and more
Information
data that has been processed and analyzed to give the facts more value and meaning - a graph of stock prices over time
common sources of data - Social Networks
Facebook, twitter, youtube, blogs, search engines, etc
common sources of data - business systems
medical records, commercial transactions, bank records, e-commerce
Common sources of data IoT
Home automation, weather sensors, traffic sensors, webcams, surveillance videos, security images
Data.gov
USA government geoportal to search for USA federal executive branch datasets provides over 250,000 US government data sets for research and development of applications
NASA's Data Portal
provides several data sets related to aeronautics, Earth sciences and space sciences
Resources
The computer hardware CPU, memory, storage space and software components that are available to the database system. To manage Verify sufficient resources exist and run database performance analysis tools to test performance relative to hardware, software and end user application usage.
Data Contention
When several users and applications are competing to use the same data. To regulate, A database system can be designed in a way to pre-emptively limit data contention.
transaction
A transaction occurs when data is altered in some fashion within a database
- Transaction Manager
Query Processor
it interprets queries, creates a strategy to modify the database or to retrieve data from the database and returns query results back to the requesting application.
Query optimization
How a database system determines the most efficient way to execute a query. To manage, A database system generates query plans (set of steps to access data) to aid optimization. A trade-off exists between spending a lot of time generating an optimal plan and getting the best results.
Storage Manager
authorises database access and interacts with the file system. It translates the instructions from the query processor into low level file system commands to modify or retrieve the database data. It manages data dictionary. databases can range from several megabytes to several terabytes so the storage manager uses an index to quickly locate data.
Data Dictionary
stores information about the structure of the database and the types of data in the database.
Transaction Manager
ensures transactions are ACID compliant, can manage concurrency through the placement of locks on data, and can handle transaction failure. It keeps a log of all transaction performed by the database system. In case of failure, the database can rebuild itself from that log.
Database architecture
Database applications can be partitioned into one, two, or three parts or "tiers"
- one tier
- two tier
- three tier
One-Tier Architecture
the database system resides on the same computer used to interact with the database. Database designers and programmers often use a single-tier architecture when developing databases and applications because of the simplicity and control of having all the components on the same machine.
two-tier architecture
the application is resident on a computer, called the client. The application on the client sends requests over the network to the database system, which resides on a computer called the server. Multiple applications can run on multiple clients, all sharing the same database.
Three-tier architecture allows many clients to share the same database.
In an application, the architecture is divided into two parts: an application at the client side and an application at the server side. The client-side application requests the data from the server-side application over a network, while the server-side application interacts directly with the database system on behalf of the client-side application. The three-tier architecture is common in web and mobile applications.
query
CRUD basically is a database command, which generally inserts, reads, updates, and deletes data from a database.
CRUD
Create: Insert new data into a database.
Identifiers
Objects from the database like tables, columns, etc.
City, Name, Population
comments
Statement intended only for humans and ignored by the database when parsing an SQL statement.
-- single line comment
/ multi-line Comment /
SQL sublanguages
Data Definition Language (DDL) defines the structure of the database.
Data Query Language (DQL) retrieves data from the database.
Data Manipulation Language (DML) manipulates data stored in a database.
Data Control Language - DCL controls the access of database users.
Data Transaction Language - DTL manages the database transactions.
Many interesting science-, technology-, engineering-, and math-oriented websites provide open-source data to the public. Which of the following sites provide that information? (Select all that apply.)
DATA.GOV, NYC Open Data, and National Cancer Institute all provide open-sourced data.
What four properties must be achieved for transactions to change data in a relational database? Select all that apply.
Those properties which have to be achieved for changing data by a set of transactions in any relational database includes Atomicity, Consistency, Isolation, Durability.
And
Which of the following is or are the parts of any database system? Select all that apply.
The various components of the database system include the query processor, the storage manager, and the transaction manager.
True or false: SQL - Structured Query Language is the standard query language to communicate with any database system.
SQL is a standard query language to communicate with the database system. True or False: The MySQL command-line client is a command-line application that comes along with the MySQL installation. It allows developers to connect to a MySQL server, perform administrative functions, and execute SQL statements.
MySQL Command-Line Client The MySQL command-line client, mysql, is an command-line application that is included with the MySQL installation. It is utilized by developers to connect to a MySQL server, perform administrative functions, and execute SQL statements.
What is a DBMS? Select only one.
A DBMS is a software application that supports the organization of data in a database.
Which of the following is/are identified from the following readings as something that may impact database performance? Select all that apply
Database performance is impacted by workload, throughput and resources. Content and data can impact performance in some cases; more context will be given to describe why that may be the case now.
artificial intelligence, and deep learning.
NYCOpenData
Data collated from New York City government. This data helps with continuing monitoring and improvements in health and wellbeing for NYC and its residents.
Database
a storage of data in a pre-defined structure grouping associated values of data. Database architecture provides for efficient management of data storing, querying, modifying and deleting.
Database Management System DBMS
commonly known as a database system, is an application software used to support data organization in a database. A database system provides added functionality to protect data and ensure data is valid.
application
software used to interface with a database system to retrieve data in a database
Database Administrator (DBA)
is responsible for protecting the database system from unauthorized users. A database administrator implements procedures for end user access and database system availability.
database designer
defines the tables, views, constraints and indexes of a database. A design for a database considers the insertions, retrievals, modifications and deletions of data.
User
are the clients of data in a database. Users request, modify, or use stored data to generate reports or information.
SQL Statement Types
CRUD - Create, Read, Update, Delete
IDE Integrated Development Environment
a programming environment that includes an editor, compiler, and debugger. In this class we will use Codio.
SQL Structured Query Language
International standard language for processing a database. It consists of 3 parts
- Data definition language, including statements that allow you to define the database and its objects: tables, views, triggers, stored procedures.
- Data manipulation language, contains statements that allow you to update and query data.
- Data control language, allows you to grant permission to a user to access particular data in the database
ACID
An acronym for atomicity, consistency, isolation, and durability properties. To have a reliable database for end users, all four properties must be achieved to change data in a database for transactions
SELECT
MySQL query that shows you how to use the simple SELECT statement to query the data from a single table.
ORDER BY
MySQL Order of command that allows the sorting of data to show the results of a SELECT in an orderly manner, be it from ascending to descending, or vice versa.
WHERE
MySQL Filtering data clause that allows the filtering of rows depending on the desired condition.
test whether a value is NULL or not by using IS NULL filtering operator
Typical business data that are usually present in database
customer, product, orders, sales order line items etc.
Examples of various day-to-day database we access
a playlist in an app on a smartphone. photos in an album on social media. online shopping cart.
MySQL
a database management system that allows you to manage relational databases. it is open source software backed by Oracle, but you can buy commercial license versions to get premium support. It can run on various platforms. It is reliable, scalable and fast.
Data Sharing
Advantages of database over text file. Databases allow end users to update and share data at the same time. A text file being edited by one user may be unavailable to others.
Data Duplication
Shared data in databases reduces data duplication. Text files are easily shared so multiple versions of the data may exist.
Data Security
Databases ensure that only authorized users may edit or read stored data, whereas text files do not have the capability to restrict who can edit or read parts of a text file.
Interaction with Data
Databases offer easier ways to access and compile stored data. This could result in increased end-user productivity. It is tedious and could reduce user productivity to read and compile data from a number of text files.
Data Corruption
Databases ensure that data stored in them is valid based on the design of the database and can recover themselves in case of file system failure. Text files may contain invalid data or get corrupted when the file system crashes.
workload
The number of users and applications the database can handle at any given time. To manage, Limit the number of concurrent users and applications accessing the database.
Throughput
The number of transactions a database can handle over time transactions per second. To manage Ensure sufficient computer resources exists to support transaction demand.
DAD220 Ch 3
Primary Key
A column or set of columns that uniquely identifies a row. In the following Employee table the primary key is ID because each employee has a unique employee ID. The position of the primary key is on the left side of this table. Position of the primary key does not matter to the database.
The primary key is determined in SQL at the time of creating the table.
Often, primary key values are used in the WHERE clause to select a particular row.
Alternate Key
If a table contains numerous unique columns, any unique column can be designated. A unique column which is not the primary key is called an alternate key
Composite Primary Key
A composite primary key is one that uses more than one column to uniquely identify records. Composite primary keys are written in parentheses. Example: (ColumnA, ColumnB)
one employee can have multiple family members so employee id is not unique. The composite primary key would be the ID and Number columns, written (ID, Number).
non-equijoin
A join where the condition does not contain an equality operator (e.g. - the operator might be greater than or less than). This kind of join combines rows which do not have equal values for the common columns.
A non-equijoin joins columns that are compared with a comparison operator other than =, such as <, >. The following example selects all buyers and properties that are priced less than the buyer's maximum price using a non-equijoin.
self-join
Joins a table to itself
In a self-join, a table is joined to itself. In a self-join, any columns of a table can be compared with other columns of the same table provided the data types of the columns are comparable. If a foreign key and the referenced primary key are in the same table, a self-join might compare key columns.
In a self-join, alias names must be utilized to distinguish between the left and right tables. In the following example, A is the left table and B is the right table. The result set contains employees with each employee's manager.
cross-join
A cross-join combines two tables without comparing columns. A cross join has no WHERE or ON clause. Consequently, all possible combinations of rows from both tables appear in the result.
The sample of output for the below example is: all configurations of iPhone models and memory appear, along with total price.
Entity Relationship Model
An entity-relationship model is a high-level representation of data requirements, ignoring implementation details. An entity-relationship model guides implementation in a particular database system, such as MySQL.
An entity-relationship model includes three kinds of objects
- An entity is a person, place, product, concept, or activity.
- A relationship is a statement about two entities.
- An attribute is a descriptive property of an entity.
The model only includes entities, relationships, and attributes that are relevant to the database.
When the model is implemented in SQL, entities typically become tables. Relationships and attributes typically become foreign keys and columns, respectively. However, some relationships and attributes become tables. Since the conversion is indirect, requirements are documented as entities, relationships, and attributes rather than tables, keys, and columns.
An attribute type is a set of values. Ex: All employee salaries.
Entity becomes a table
relationship becomes a foreign key
attribute types columns
An instance is an element of a set:
An entity instance is an individual thing. Ex: The employee Sam Snead.
A relationship instance is a statement about entity instances. Ex: "Maria Rodriguez manages Sales."
An attribute instance is an individual value. Ex: The salary $35,000.
Entity instance rows
relationship instance foreign key values attribute instances column values
database design
Complex databases are developed in two phases:
- Analysis models the data requirements as an entity-relationship model without the consideration of implementation details.
- Implementation changes the entity-relationship model into tables, columns, and keys for a specific database system.
Analysis
Analysis is particularly helpful for large databases that have hundreds of users and where the requirements need to be documented. For small databases that have just a few tables and users, analysis is less applicable and is thus often omitted.
Analysis Steps
Identify Entities, Relationships and Attributes
Determine Cardinality
Identify dependent and independent entities
create superset and subset entities
Implementation Steps
Implement Entities
Implement Relationships
Implement Attributes
Review tables for third normal form
Analysis considers details of a specific database T F
False Analysis documents database requirements, without considering implementation details.
An entity-relationship model is developed for all database design projects. T F
False An entity-relationship model is developed in the analysis phase. Analysis is sometimes omitted for simple databases with just a few users and tables.
