C170 Data Management Applications Exam 2025

Database model - -a database model is a conceptual framework for database software. Nosql databases - -stands for not only sql. Nosql is a non-relational database. Created out of necessity for big data and is optimized for big data. Encompasses a variety of database models. Nosql is not the dominant model for databases. Example of the 4 database models - - Hierarchical - speed and storage - ims Network model - speed and storage objective - idms. Relational model - productivity and simplicity, transactional applications - mysql, oracle, sql server, db2. Nosql models - big data, analytic applications - mongodb, redis, cassandra, neo4j Which database is relational? Db2, idms, or mongodb... - -db2 - db2 is one of the first relational databases, released by ibm in the early 1980's The relational model was originally developed for which types of applications? - transactional applications like banking and airline reservations. What was the initial impediment to commercial adoption of relational databases in the early 1980's? - -processing speed Relational model is a database model based on mathematical principles, with three parts: - - 1. A data structure that prescribes how data is organized 2. Operations that manipulate data structures. 3. Rules that govern valid relational data. Set theory - what is a set? - -a set is a collection of values, or elements, with no inherent order. Set's are denoted with braces. Ex: {apple, banana, lemon} is a set of 3 fruit. Relational model contains relational data structure based on 3 mathematical concepts: what are the 3 concepts? - - Domain Tuple Relation C170 C170 Domain - -named set of possible database values. Integers, dictionary words, logical values such as true/false. Tuple - -finite sequence of values, each drawn from a fixed domain. Ex. (3, apple, true) Relation - -a named set of tuples, all drawn from the same sequence of domains. Ex. Name of the tuple (3, apple, true) is grocery, as is the name of the tuple (0, lemon, false). Each tuple position is called an - -attribute Which terms are commonly used in relational database processing - -row, table, column. Occasionally tuple, relation, attribute adn record, file, field are also used. Are these relations the same? { (8, mango, false), (-11, watermelon, false) }{ (-11, watermelon, false), (8, mango, false) } - -yes. Both relations contain the same tuples. Since a relation is a set, the order of the tuples does not matter. In the relational data structure, which components are named? - -domain, relation, and attribute Relational algebra includes the following 7 relational operations: - -select Project Product Join Union Intersect Difference Select - -relational operation aka relational algebra term. Means to select a subset of rows of a table. Project - -relational operation aka relational algebra term. Eliminates one or more columns of a table. Product - -relational operation aka relational algebra term. Lists all possible combinations of rows of two tables. Join - -relational operation aka relational algebra term. A product operation followed by a select operation. Select statement that combines data from two tables, known as the left table and the right table, into a single result. C170 C170 Example: Select department name, employeename From department, employee Where manager = id; Union - -relational operation aka relational algebra term. Combines two tables by selecting all rows of both tables. Intersect - -relational operation aka relational algebra term. Combines two tables by selecting only rows common to both tables. Difference - -relational operation aka relational algebra term. Combines two tables by selecting rows that appear in one table but not the other. What is the result of a relational operation? - -table Relational rules (integrity rules) - -logical constraints that ensure data is valid and conforms to business policy Structural rules - -relational rules that govern data in every relational database. Ex: unique primary key, unique column names, no duplicate rows. Business rules - -relational rules specific to a particular database and application. Ex: unique column values, no missing values, delete cascade. Data in relational database can violate relational rules - -true. Depending on how tables are specified, some relational rules can be violated. Ex: a table with no primary key can be created. However, rule violations often create business problems and should normally be avoided or automatically prevented. Equivalent relational terms - -domain, data type Tuple, row, record Relation, table, file Attribute, column, field Is this a structural rule or business rule? 1. Different columns of the same table must have different names 2. Values may not be repeated in a particular column 3. All tables should have a primary key used to identify individual rows. - -business rules (more table values) structure (more structure of table itself) 1. Structural 2. Business rule 3. Structural C170 Table - -collection of data organized as columns and rows A table must have at least one column and any number of rows. A table without rows is called an empty table. Column - -set of values of the same type. Each column has a name, different from other column names in the table. Components of a column are: name and data type Row - -is a set of values, one for each column Cell - -is a single column of a single row. In relational databases, each cell contains exactly one value. Must a table have at least 1 row? - -no. A table without rows is called an empty table. A table must have at least one column and any number of rows. How often do column names and data types change? - -occasionally - not often How does a database administrator specify column names and data types? - -with the sql language 7 datatypes - -integer Decimal Character Time Binary Spatial Document Integer - -int = 4 bytes, smallint = 2 bytes Decimal - -numbers with fractional values. Vary by number of digits after the decimal point and max size. Types include float and decimal. Character - -text characters. Char, a fixed string of characters and varchar, a string of variable length up to a specific maximum size. Time - -data types represent date, time, or both. Date, time, datetime, and timestamp. Binary - -data types store data exactly as the data appears in memory or computer files, bit for bit.. Blob, binary, varbinary, and image. C170 C170 C170 Spatial - -store geometric information, such as lines, polygons, and map coordinates. Polygon, point, and geometry. Relatively new and consequently vary greatly accross database systems. Document - -contain textual data in a structured format such as xml or json T/f: the char data type represents a variable string of characters - -false. Char is a string of characters of fixed length. Ex: char(6) T/f: a binary data type stores data as an exact copy of computer memory. - -true. Structural rules for tables (3) - -1. Tables are normalized - exactly one value exists in each cell. 2. No duplicate column names - same column name can appear in different tables though. 3. No duplicate rows - no two rows may have identical values in all columns. Principle of data independence - -rows and columns of a table have no inherent order. The sequence is arbitrary and does not affect the results of a database query. How many values may appear in each cell? - -exactly one value Where are duplicate column names allowed? - -in different tables Quiz: what does the principle of data independence state? - -the result of a database query is not affected by the physical organization of data on storage devices. Null - -value that represents missing data. Either 'unknown' or 'inapplicable'. Null does not mean 0. Operator - -is a symbol that computes a value from one or more other values, called operands. Ex: arithmetic + - * /, Ex: comparison =, true, false. Ex: logical and, or, and not Aggregate function - -operates on numeric values from multiple rows, including only rows selected by the where clause. Aggregate functions include: sum, avg, max, min. What is sum(null + 4200) = - -null What is sum(null) + sum(4200) = - -4200 Primary key - -a column or group of columns that identifies a unique row in a table