★ Must-know
📌 Information is data interpreted and assembled in a meaningful way, such as aggregated and analyzed customer names and purchase amounts that reveal purchasing trends.
Further detail
The five database types listed are:
The stated database advantages are:
A typical database system comprises:
DBMS functions include:
★ Must-know
📌 Logical data independence permits changes to the conceptual schema without changing external schemas or application programs, whereas physical data independence permits changes to storage methods and structures without changing the conceptual schema.
Further detail
External views serve users, while the internal level governs physical storage.
★ Must-know
📌 Functional requirements specify system behavior and services, whereas non-functional requirements specify operational qualities and constraints such as performance, reliability, and usability.
Further detail
Database systems support structured storage, retrieval, and management for operations and decisions, including e-commerce transactions, customer relationship management, data mining, and blockchain transaction integrity and transparency.
Examples of non-functional requirements include responding to user queries within two seconds and handling at least 10,000 concurrent users without performance degradation.
System requirement categories include:
Requirements are prioritized as high when they deliver immediate business value and address critical user needs, medium when essential but not time-sensitive, and low when they are nice-to-have and deferrable without significant impact on initial rollout.
★ Must-know
📌 In relational terminology, degree is the number of attributes in a relation, whereas cardinality is the number of tuples.
Further detail
Data models typically include entities, attributes, and relationships: entities are objects or concepts with stored data, attributes are their properties, and relationships describe how entities interact.
The six data model types listed are:
The relational model's fundamental concepts are:
A relation has these properties:
★ Must-know
Further detail
Entities are typically shown as rectangles in ERDs, while their attributes are shown as ovals connected to them.
An Address composite attribute can be divided into Street, City, and Postal Code.
Age can be derived from the DateOfBirth attribute.
A PhoneNumber attribute can contain multiple numbers for one entity.
★ Must-know
📌 A unary relationship involves one entity relating to itself, a binary relationship involves two entities, and a ternary relationship involves three entities.
Further detail
Relationships are typically depicted as diamond shapes linking entity rectangles.
In a one-to-one (1:1) relationship, each entity instance is associated with one instance of the other entity.
In a one-to-many (1:M) relationship, one entity instance can be associated with multiple instances of another entity, while each instance on the many side can have one instance on the one side.
In a many-to-many (M:N) relationship, instances of either entity can be associated with multiple instances of the other.
★ Must-know
📌 Generalization is a bottom-up process that groups shared characteristics of specialized entity types into a supertype, whereas specialization is a top-down process that divides a supertype into subtypes with specialized characteristics.
📌 Under a disjoint constraint, a supertype instance can belong to only one subtype; under an overlap constraint, it can belong to more than one subtype.
Further detail
Generalization builds upward from subtypes; specialization branches downward from a supertype.
★ Must-know
Further detail
📌 A single table with columns for all subtypes is suggested when the number of subtypes is small and overlap exists.
📌 Separate tables for each subtype are suggested when the specializations are radically different and involve many unique attributes.
Entities become tables, relationships become keys or tables, then normalize and enforce constraints.
★ Must-know
📌 An update anomaly occurs when redundant instances of data are not all updated, a deletion anomaly occurs when deleting data unintentionally removes other data, and an insertion anomaly occurs when data cannot be added without other data being present.
Further detail
The anomaly trio: UDI — Update, Deletion, Insertion.
★ Must-know
A use case diagram contains actors, use cases, and a system boundary; associations connect actors to use cases, and use case relationships include extend, include, and generalization.
Use case relationships include:
Further detail
📌 UML structural diagrams represent static aspects of a system, while behavioral diagrams represent its dynamic behavior.
Include is encapsulated functionality; extend adds conditional or optional behavior.
★ Must-know
📌 Aggregation is a whole-part association whose part can exist independently of the whole, whereas composition ties the part's lifecycle to the whole.
Further detail
Class names should be intuitive nouns that reflect the class's role or function.
A dependency means one class relies on another to function, and changes in the supplier class can affect the dependent class.
Generalization is an inheritance relationship in which a subclass inherits attributes and behaviors from a superclass.
Association is a structural relationship indicating that objects of one class connect to objects of another class.
Creating an effective class diagram involves defining clear class names, listing relevant attributes, outlining behaviors, depicting relationships with appropriate connectors, and reviewing the diagram against the system architecture.
Aggregation parts can live independently; composition parts depend on the whole.
★ Must-know
Further detail
📌 A DFD must not show data moving directly between data stores or between external entities in different levels, and it must not include control elements such as decision symbols.
📌 DFDs should use consistent labels for data stores, processes, and flows, and their flows should connect logically to processes with direction and necessary data stores represented.
★ Must-know
📌 DQL queries data, DDL defines database structures, DML manipulates data, and DCL manages rights and permissions.
📌 A UNIQUE constraint maintains unique column values and can be used multiple times in a table, whereas a primary key uniquely identifies records.
Further detail
SQL was developed in the 1970s and standardized by the American National Standards Institute (ANSI).
The CREATE DATABASE statement establishes a new database, as in CREATE DATABASE SchoolDB;.
📌 The DROP DATABASE statement deletes an existing database and its contents, and the action is irreversible, so data should be verified before execution.
The Students table example defines StudentID as an INT primary key, FirstName and LastName as VARCHAR(50), and DateOfBirth as DATE.
A NOT NULL constraint prevents a column from containing NULL, making a value mandatory when a row is inserted.
A DEFAULT constraint assigns a predetermined value during insertion when no value is supplied for the column.
A CHECK constraint restricts column values to those satisfying its specified condition, such as a GradeValue between 0 and 100.
A FOREIGN KEY constraint establishes a relationship by referencing a primary key in another table.
ALTER TABLE modifies an existing table's structure by adding or dropping columns and constraints.
AUTO_INCREMENT automatically generates sequential integer values for a column, typically a primary key, when rows are inserted.
★ Must-know
Data Manipulation Language operations include INSERT to add records, UPDATE to modify existing data, DELETE to remove records, and SELECT to retrieve data.
A SELECT statement retrieves specified columns from a table using the form SELECT column1, column2 FROM table_name.
A WHERE clause filters rows so that a query returns only records satisfying its specified conditions.
Further detail
SQL operators include arithmetic operators +, -, *, and /, and logical operators AND, OR, and NOT.
SELECT INTO selects data and inserts it into a new table, as in SELECT * INTO backup_table FROM original_table.
SELECT DISTINCT removes duplicate rows from a result set so that only unique records are returned.
ORDER BY sorts query results by one or more columns in ascending order by default or descending order when specified.
📌 AND returns rows only when all combined conditions are true, whereas OR returns rows when at least one condition is true.
★ Must-know
Common aggregate functions are:
GROUP BY groups rows with the same values in specified columns into summary rows for use with aggregate functions.
📌 WHERE filters rows, whereas HAVING filters groups based on aggregate values.
Further detail
📌 INNER JOIN returns rows with matching values in both tables; LEFT JOIN retains all left-table rows, RIGHT JOIN retains all right-table rows, and FULL OUTER JOIN retains rows from both tables, using NULLs for non-matches.
★ Must-know
INSERT adds rows to a table, UPDATE modifies existing rows, and DELETE removes rows.
Database security protects data integrity, confidentiality, and availability through measures including permissions, access control, and encryption where necessary.
Further detail
📌 Assigning users roles according to their responsibilities and the principle of least privilege enhances database security by managing access.
★ Must-know
A user interface is the primary point of interaction with a database and translates user queries and inputs into actions the database can process.
Effective database input forms use clear labels, logical organization, intuitive navigation, validation, and real-time feedback to support accurate data entry and prevent errors.
Further detail
Database normalization, entity relationships, and indexing influence how data is retrieved and displayed through the user interface.
Wireframes help teams plan functionality, user flow, and content placement, identify usability issues, and communicate a shared understanding before detailed design.
★ Must-know
Database testing evaluates functionality, performance, and reliability against user and system requirements and can reveal issues before deployment.
Testing types include:
Further detail
📌 Testing types describe testing methodologies or focus, whereas testing levels describe the stages at which testing progresses from units to the complete integrated system.
Software documentation can provide: improved communication, facilitated onboarding, enhanced user experience, increased maintainability and scalability, a record for future reference or regulatory compliance
Product documentation typically covers: technical specifications and requirements, hardware and software environments, configuration settings and guidelines, installation and deployment instructions, operational procedures and troubleshooting tips
Types of user documentation include:
Creating a good user manual involves defining its audience, outlining a logical and navigable structure, using clear concise language, adding visual aids, and testing usability with real users.
Technical documentation serves IT professionals and developers with information on system design, architecture, and code, including architecture diagrams, code annotations, APIs, database schemas, and security and compliance.
An SDD includes: a system architecture overview, module descriptions and interactions, design patterns and methodologies, interface designs such as user interfaces and APIs, non-functional requirements such as performance, scalability, and security
API documentation gives external developers information needed to integrate with and use software services, including endpoints, request-response formats, authorization, error handling, examples, and usage policies.
User documentation guides software users; technical documentation serves IT professionals and developers.
| Concept | Definition | Example |
|---|---|---|
| Unary degree | One entity relates to itself | A manager manages employees |
| Binary degree | Two entities participate | Student and Course |
| Ternary degree | Three entities participate | Doctor, Patient, and Hospital |
| 1:1 cardinality | One instance links to one counterpart | One husband and one wife in the stated example |
| 1:M cardinality | One instance links to multiple instances on the other side | One mother and many children |
| M:N cardinality | Multiple instances on both sides can be linked | Students and courses |
| Relationship | Meaning | Key feature |
|---|---|---|
| Dependency | One class relies on another | Supplier changes can affect dependent class |
| Generalization | Subclass inherits from superclass | Inheritance of attributes and behaviors |
| Association | Objects of classes are structurally connected | Connection between classes |
| Aggregation | Whole-part association | Part exists independently of whole |
| Composition | Strong whole-part relationship | Part's lifecycle is tied to whole |
Take a 11-question practice quiz on ERD, Normalization and Modeling, with every answer explained.
1. A retailer records customer names and purchase amounts, then aggregates and analyzes them to identify buying trends. What do the resulting trends represent?
2. Two departments maintain separate files containing the same supplier details. One updates a supplier’s address while the other does not, and users encounter conflicts when editing shared records. Which file-system problem does this situation illustrate?
Memorize the key concepts of ERD, Normalization and Modeling with 10 flashcards.
How does information differ from raw data?
It is data interpreted and assembled meaningfully.
Which three levels make up database architecture?
External, conceptual, and internal.
What are data before they are organized?
Raw, unprocessed facts.
Upload your class notes and Revizly writes a study guide, a quiz and flashcards in about a minute.
Study guide maker