Three types of media are most important for database management: Main memory, also called random-access memory (RAM), is the primary memory used when computer programs execute. Main memory is fast, expensive, and has limited capacity.
Storage Computersmediause
DAT 390 Entire Course Link
**********************************************
DAT 390 Wk 1 - Practice: Reading
Database Integration with Other Systems
a variety of media to store data, such as random-access memory, magnetic disk, optical disk, and magnetic tape. Computer media vary on four important dimensions:
DAT/390
The Latest Version A+ Study Guide
https://www.onlinehelp123.com/dat-390
Capacity. In principle, any media type can store any amount of data. In practice, capacity is limited by cost. Expensive media have limited capacity compared to inexpensive media.
Volatility Volatile memory is memory that is lost when disconnected from power. Non-volatile memory is retained without power.
**********************************************
Speed. Speed is measured as access time and transfer rate. Access time is the time required to access the first byte in a read or write operation. Transfer rate is the speed at which data is read or written, following initial access.
Cost. Cost typically ranges from pennies to dollars per gigabyte of memory, depending on the media type.
Accurately complete the 1.1 to 1.7 Reading, Participation, and Challenge Activities to receive full points for this practice. You have unlimited attempts. The points you see in zyBooks do not reflect the points you receive in the gradebook.
In the EXPLAIN result, column key is null, indicating the query does not use the idx_title index. Column rows is 100, indicating all rows are read. Since the query has > in the WHERE clause rather than =, the query executes a table scan and is slow.
DAT 390 Wk 1 - Apply: Labs
Complete zyLab 1.8-1.9 in zyBooks. You have unlimited attempts in Develop mode. Toggle to Submit mode for a grade. Refer to the film table of the Sakila database. This lab loads film with 100 rows from Sakila. Consequently, SELECT * FROM film; generates too many characters to display in the zyLab environment. However, statements with less output, such as SELECT title FROM film;, execute successfully.
In the EXPLAIN result, column key has value idx_title, indicating the query uses the index on title. Column rows is 1, indicating only one table row is read. The query is fast.
Magnetic disk, also called hard-disk drive (HDD), is used to store large amounts of data. Magnetic disk is slower, less expensive, and higher capacity than flash memory.
4. Explain the query SELECT * FROM film WHERE title > 'ALONE TRIP';.
Write and run seven SQL statements:
2. Create an index idx_title on the title column.
This lab illustrates the use of indexes and EXPLAIN to optimize query performance. Refer to EXPLAIN documentation for information about EXPLAIN result columns.
5. Explain the query SELECT rating, count(*) FROM film GROUP BY rating;
In the EXPLAIN result, column key is null, indicating no index is available for the query. Column rows is 100, indicating all rows are read. The query executes a table scan and is slow.
Flash memory, also called solid-state drive (SSD), is less expensive and higher capacity than main memory. Writes to flash memory are much slower than reads, and both are much slower than main memory writes and reads.
1. Explain the query SELECT * FROM film WHERE title = 'ALONE TRIP';.
3. Explain the query of step 1 again.
Main memory is volatile. Flash memory and magnetic disk are non-volatile and therefore considered storage media. Databases store data permanently on storage media and transfer data to and from main memory during program execution.
DAT 390 Wk 2 - Practice: Reading
Ex:data.The
debit-credit transaction transfers funds from one bank account to another. The first operation removes funds, say $100, from one account, and the second operation deposits $100 in another account. If the first operation succeeds but the second fails, $100 is mysteriously lost. The transaction must complete and save either both operations or neither operation.
A transaction is a sequence of database operations that must be either completed or rejected as a whole. Partial execution of a transaction results in inconsistent or incorrect
6. Create an index idx_rating on the rating column.
Saving complete transaction results in the database is called a commit. Rejecting an incomplete transaction is called a rollback. A rollback reverses the transaction and resets data to initial values. A variety of circumstances cause a rollback:
When a failure occurs, the database is notified and rolls back the transaction. If the failure is temporary, such as intermittent network problems, the database attempts to restart the transaction. If the failure is persistent, such as a deleted bank account, the databases 'kills' the transaction permanently.
The database detects a conflict between concurrent transactions. Ex: Two airline customers attempt to reserve the same seat on a flight.
The application program detects an unsuccessful database operation. Ex: In the debit-credit transaction, funds are removed from the debit account, but the credit account is deleted prior to deposit.
The operating system detects a device failure. Ex: Magnetic disk fails during execution of a transaction, and transaction results cannot be written to the database.
In the EXPLAIN result, column key is null, indicating no index is available for the query. Column rows is 100, indicating all rows are read. The query executes a table scan and is slow.
In the EXPLAIN result, column key has value idx_rating, indicating the query reads rating values from the index. The query uses an index scan, which is faster than a table scan (step 5).
Accurately complete the 2.1 to 2.5 Reading, Participation, and Challenge Activities to receive full points for this practice. You have unlimited attempts. The points you see in zyBooks do not reflect the points you receive in the gradebook.
7. Explain the query of step 5 again.
The storage engine executes instructions, manages indexes, and interacts with the file system. Some storage engines support database transactions, described elsewhere in this material.
4. Select all actors.
Start a transaction and:
2. Set a SAVEPOINT.
The actor with first name 'CUBA' should appear in the second SELECT but not the first.
6. Select all actors a second time.
Accurately complete the 3.1 to 3.7 Reading, Participation, and Challenge Activities to receive full points for this practice. You have unlimited attempts. The points you see in zyBooks do not reflect the points you receive in the gradebook. Architecture describes the components of a computer system and the relationships between components. This section describes MySQL architecture. Other relational databases have similar components, but component details and relationships vary MySQLgreatly.
The file system contains system and user data, such as log files, tables, and indexes.
1. Insert a new actor with values 999, 'NICOLE', 'STREEP', '2021-06-01 12:00:00'
DAT 390 Wk 2 - Apply: Labs
5. Roll back to the savepoint.
components are organized in four layers:
Complete zyLab 2.6 and 2.7 in zyBooks. You have unlimited attempts in Develop mode. Toggle to Submit mode for a grade. Refer to the actor table of the Sakila database. The table in this lab has the same columns and data types but fewer rows.
DAT 390 Wk 3 - Practice: Reading
Tools interact directly with database users and administrators, and send queries to the query processor.
The query processor manages connections from multiple users and compiles queries into low-level instructions for the storage engine.
3. Delete the actor with first name 'CUBA'.
o LessonDateTime - partial primary key
2. with columns: primary with columns:
MySQL is available in a free version, called MySQL Server, and a paid version, called MySQL Enterprise Edition. The Enterprise Edition includes MySQL Server and components for high-end commercial installations, such as:
StudentID
Edition components provide advanced support for backup, security, encryption, and firewall. Enterprise Edition components are intended for database administrators, not users.
o ID
o ID -
key o FirstName o LastName o Street o City o State o Zip o Phone o EmailAddress 3. LessonSchedule
DAT 390 Wk 3 - Apply: Labs
o HorseID - partial primary key, foreign key references Horse(ID)
o - foreign key references Student(ID)
o RegisteredName o Breed o Height o BirthDate
1. Horse with columns: - primary key
Student
Audit keeps track of all database changes. For each change, Audit tracks the time of change and who made the change. Audit supports government and business audit requirements for sensitive databases such as financial, medical, and Additionaldefense.Enterprise
The database has three tables for tracking horse-riding lessons:
Monitor collects and displays information on CPU, memory, and index utilization, as well as queries and results. Database administrators use Enterprise Monitor to manage and tune large databases with many users.
Complete zyLab 3.8 and 3.9 in zyBooks. You have unlimited attempts in Develop mode. Toggle to Submit mode for a grade.
Character types represent textual characters. Character types may be either fixed-length or variable-length strings, consisting of either single-byte (ASCII) or double-byte (Unicode) characters.
Integer types represent positive and negative integers.
Semantic types are based on other types but have a special meaning and functions. Ex: MONEY has decimal values representing currency. BOOLEAN has values zero and one representing false and true. UUID has string values representing Universally Unique Identifiers, such as a0eebc99-9c0b-4ef8-bb6d6bb9bd380a11. ENUM has a fixed set of string values specified by the database designer, such as 'red', 'green', 'blue'.
Database functions can decompose the internal structure into separate values. Ex: If BirthDate has type date, the SQL function month(BirthDate) might return the birth month. From the perspective of arithmetic and comparison operators, however, the internal structure is ignored and each value is considered atomic.
Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student's first and last names, and the horse's registered name. Order the results in ascending order by lesson date/time, then by the horse's registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results.
DAT 390 Wk 4 - Practice: Reading
Binary types store data exactly as the data appears in memory or computer files, bit for bit. Ex: Binary types may be used to store images.
Accurately complete the 4.1 to 4.7 Reading, Participation, and Challenge Activities to receive full points for this practice. You have unlimited attempts. The points you see in zyBooks do not reflect the points you receive in the gradebook.
Time types represent date, time, or both. Some time types include a time zone or specify a time interval.
Since the 1980s, relational database products have supported six broad categories of data types:
Simple types
Decimal types represent numbers with fractional values.
The above types have relatively simple internal structures and thus are called simple data types. Ex: A character value consists of a series of individual characters. Ex: A date value has three parts, year, month, and day.
Declarative languages do not contain control flow statements. Each statement declares what result is desired, using logical expressions, rather than how the result is processed. Compilers for declarative languages are called optimizers, since the compiler determines an optimal way to process each declarative statement. SQL is the leading example of a declarative language.
Accurately complete the 5.1 to 5.7 Reading, Participation, and Challenge Activities to receive full points for this practice. You have unlimited attempts. The points you see in zyBooks do not reflect the points you receive in the gradebook.
RegisteredName - variable-length string
DAT 390 Wk 4 - Apply: Labs
Breed - variable-length string
DAT 390 Wk 5 - Practice: Reading
2. Object-oriented languages organize code into classes. A class combines variables and procedures into a single construct. Most languages developed since 1990 are object-oriented. Ex: Java, Python, and C++.
Height - decimal number
Complete zyLab 4.8 and 4.9 in zyBooks. You have unlimited attempts in Develop mode. Toggle to Submit mode for a grade. The Horse table has the following columns:
ID - integer, primary key
Programming languages
Programming languages fall into two broad categories, or paradigms: imperative and Imperativedeclarative.languages
Write a SELECT statement to select the registered name and height for only horses that have an above average height. Order the results by height.
1. Procedural languages are composed of procedures, also called functions or subroutines. Most languages developed prior to 1990 are procedural. Ex: C and COBOL.
BirthDate - date
contain control flow statements that determine the execution order of program steps. Control flow statements include loops for repeatedly executing code and conditionals for conditionally executing code. Two popular types of imperative languages are:
Id integer primary key, not null 1
2. create_table() creates the Horse table.
Height double 15.3
Breed text 'Quarter horse'
3. insert_horse() inserts one row into Horse.
Complete all four functions. Function parameters are described in the template. Do not modify the main program.
Name text 'Babe'
The Horse table should have five columns, with the following names, data types, constraints, and values:
Complete zyLab 5.8 and 5.9 in zyBooks. You have unlimited attempts in Develop mode. Toggle to Submit mode for a grade. Complete the Python program to create a Horse table, insert one row, and display the row. The main program calls four functions:
1. create_connection() creates a connection to the database.
4. select_all_horses() outputs all Horse rows.
DAT 390 Wk 5 - Apply: Labs
Name Data type Constraints Value
BirthDate text '2015-02-10'