DATA MANAGEMENT
Database Management
Components
- Data:
o Structured (model behind)
o Unstructured (videos, sound)
o Internal / External
- Informaton: organized data,
meaning and value
- Knowledge: processed data, informaton is
applicable to business decision problems
Taxonomy
- Descriptve analytcs: use data to understand past and present (reportng, SQL, data warehouse)
- Predictve analytcs: predict future behavior based on past performance (tme series)
- Prescriptve analytcs: make decisions to achieve best performance (operatons, research, AI)
Process & Product
- Process: BI tools + applicatons + techniques » BI soluton
o Warehousing, SQL, reportng, digital dashboards, statstcs, visualizatons, etc.
- Product: BI process » informaton + knowledge (enables decision making)
o Understanding customer preferences, growth opportunites, internal efciency, etc.
Relational databases
- Database is environment with multple tables that can be connected to each other
- Allow data to be grouped into tables and set relatonships between tables
- Relatonal model enables you to view data logically rather than physically
- Keys: one or more atributes that determine other atributess A » B,C,D (functonally dependent)
Types of keys
- Primary key: uniquely identies each record in a table (can never be null, chosen from candidate)
- Super key: one or more atributes that uniquely identfy each row in a table
- Candidate key: super key without unnecessary atributes
- Foreign key: atributes whose values match the primary key values in the related table
o Employee: EMP (PK)s JOB (FK)
o Job: JOB (PK)
o Beneit: EMP+PLAN (PK)s EMP, PLAN (FK)
o Plan: PLAN (PK)
- Table relatonships:
o One-to-one: separate tables (driver : car)
o One-to-many: common (course : classes
o Many-to-many: not supported directly
, Data Warehouses
Structure Query Language [SQL]
Basic form
SELECT * Get data out of the table, select ields/columns you want
FROM Purchase Fill in the name of the table
WHERE Product=”Bagel” Conditon
To eliminate duplicates, use the word SELECT DISTINCT
Aggregate functons Grouping
SELECT Sum (Price * Quanttt) AS TotalSales
FROM Purchase
GROUP BY Product
Conditon on grouping
SELECT Product, Sum (Price * Quanttt) AS TotalSales
FROM Purchase
GROUP BY Product
HAVING Sum (Price * Quanttt) > 20
Joining database tables
SELECT Name, LastName
FROM Plater, Team
WHERE PlatDat = “Mondat” AND Plater.TeamCode = Team.TeamCode
Types of joins
- Inner join » Retain only rows in both sets
- Left join » Join matching rows from b to a
- Right join » Join matching rows from a to b
- Full outer join » Retain all values, all rows
- Minus » All rows in a that don’t have a match in b
Data Warehouse
- Database that is set up separately from the organizaton’s databases for decision making
- Consolidaton: connect all diferent data to select most important variables for decision making
- Quality: diferent sources » inconsistent representatons / codes / formats » improve this!
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller JHessels. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $4.27. You're not tied to anything after your purchase.