Core Concepts and Structures in Database Design, Explained

UPI Study › Courses › System Analysis and Design › Free lessons › Lesson 6.1 Free with a free account

Core Concepts and Structures in Database Design

System Analysis and Design · Chapter 6: Database Design, Normalization, and Business IntelligenceSection 6.1 · lesson 39 of 697 min · free with a free account

← Service-Oriented vs. Client-Server Architecture: A ComparisonNext lesson: Database Management Systems: Purpose, Functions, and Scope →

Earn 3 college credits for System Analysis and DesignRecommended for credit by NCCRS · Computer Science 390 · self-paced · one payment, lifetime access
Enroll in System Analysis and Design · $250 →Download the syllabus (PDF)

In this lesson

Why databases, the data hierarchy, keys, integrity, and data models.

  • Contrast file-based and database approaches to storing data
  • Describe the data hierarchy, from bit to database
  • Define primary, foreign, candidate, and composite keys, and integrity constraints
  • Compare hierarchical, network, relational, and object data models

Transcript

Everything said in the video, section by section (956 words). Read along, or search it.

Database Design, Normalization, and Business Intelligence

Core Concepts in Database Design

Almost every information system is built on a database. This chapter is about designing that database well. We start with the core concepts. Why databases replaced separate data files, how data is organized from the smallest bit upward, and the keys that identify and connect records. Then the integrity rules a database enforces, and the four data models databases have used over time.

Before Databases

File-Based vs. Database Approaches

Before database management systems became standard, each application typically kept its own separate data files. A billing application might keep its own customer file. A shipping application kept a separate, only loosely related customer file of its own. So the same data lived in several places, and nothing guaranteed those copies stayed in agreement. Change a customer's address in billing, and shipping might never find out. The database approach instead centralizes data into a shared collection, managed by a database management system, or D B M S. Multiple applications and users then work from the same consistent data, rather than their own copies. Redundancy drops, because data is stored once and shared. Consistency is maintained centrally, by the D B M S. And access control is centralized too, rather than handled separately by each application.

Figure 6.1

The Data Hierarchy

Data in a database is organized in layers, each one built from the layer below it. At the bottom is the bit, a single binary zero or one. A group of bits forms a byte, representing a single character, such as the letter A. Characters form a field, a single piece of data describing one characteristic, like a customer name. Related fields form a record, a complete description of one instance, such as one customer. Related records form a table, such as the customers table. And related tables, organized and managed together, form the database. Each level is built from one or more instances of the level nested inside it.

Identifying Records

4 Kinds of Key

Now to keys, which identify records and connect tables. A candidate key is any column, or minimal set of columns, that could uniquely identify each record. A table can have more than one. For a student, both student I D and email could each uniquely identify them. The primary key is whichever candidate key is actually chosen for that role. Here, student I D. A foreign key is a field in one table that references the primary key of another table, creating a relationship between them. For example, enrollments dot course I D references courses dot course I D. And a composite key is a key made of two or more columns together. It is used when no single column is enough to guarantee uniqueness on its own.

Figure 6.2

Keys in a Small Relational Design

Here are all four ideas in one small design. Students have their own primary key, student I D, plus a name and an email. Courses have their own primary key, course I D, plus a title. Enrollments connects them. Its primary key is a composite of student I D and course I D together. A student can take many courses, and a course has many students, but each pairing appears only once. And each of those two columns is also a foreign key, referencing back to its source table. Enrollments also records the enroll date.

Rules the DBMS Enforces

Integrity Constraints

Integrity constraints are rules the D B M S enforces automatically, to keep data accurate and consistent. That way, every application does not have to enforce them independently. Entity integrity says every table's primary key must be unique, and cannot be null. Referential integrity says a foreign key must either match an existing primary key in the referenced table, or be null. Domain integrity says a column's values must fall within an allowed set, type, or range. Referential integrity is what stops an enrollments record from being created, with a course I D that does not exist in courses at all. It keeps the relationships in the design trustworthy, rather than merely intended.

4 Data Models

How a Database Structures Its Data

A data model is the overall structure a database uses to organize its data. Four models appear across the history of database systems. The hierarchical model is a tree, where each child record has exactly one parent. It is fast for strictly hierarchical data, but cannot represent many-to-many relationships naturally. The network model is a graph, where a child record can have multiple parents. It is more flexible, but complex to navigate and maintain. The relational model uses tables, linked through keys. It offers flexible querying and a strong theoretical foundation, though complex data can require many joins. The object model uses objects that bundle data and behavior, mapping naturally onto object-oriented code, but it is less standardized. The relational model has been the dominant choice since the nineteen eighties and nineties. It is the model the rest of this chapter builds on.

Key Terms

The Vocabulary of This Section

Let's lock in the key terms. A D B M S is software that manages shared data for many applications and users. A field is one characteristic, a record is one instance, and a table is a collection of related records. A candidate key could identify each record. The primary key is the one chosen to. A foreign key references another table's primary key. A composite key is made of two or more columns. And referential integrity requires every foreign key to match an existing primary key, or be null.

Key Takeaways

What to Carry Forward

To pull this together. A database approach centralizes shared data under a D B M S, reducing the redundancy and inconsistency of separate application files. The data hierarchy runs from bit, to byte, to field, to record, to table, to database. Primary, foreign, candidate, and composite keys identify records, and connect tables. Entity, referential, and domain integrity are enforced automatically, keeping data accurate and consistent. And of the four data models, the relational model is the dominant choice today.

About System Analysis and Design at UPI Study

System Analysis and Design (Computer Science 390) is a self-paced online course of 69 video lessons in 9 chapters, from the analyst's role and requirements to UML modeling, interface and database design, testing, maintenance and project management. It carries a NCCRS credit recommendation for 3 semester credits.

Every lesson transcript is free on pages like this one, and the videos are free with a free UPI Study account. Enrolling adds a practice quiz after each lesson, the graded work and final exam that earn the credit, lifetime access, and an official transcript on completion. UPI Study has helped over 130,000 students with college credits. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.

Enroll now → Course details Download the syllabus All Computer Science courses Every course with PRO →

Compare: Software Engineering vs Systems Analysis

Why take it here

Why students take System Analysis and Design with UPI Study

From business problem to working systemSystem Analysis and Design teaches how analysts study a business need and turn it into a system design: requirements, UML and data-flow models, interface and database design, implementation and project management. It is a core course in information systems and IT degrees.
Credit at a fraction of the priceThe same 3 credits can cost $750 or more at a US college. Here it is one payment, self-paced, with lifetime access.
Finish your degree soonerTransfer credits reduce the courses you still owe, so you can graduate earlier or lighten a heavy semester.
Strengthen an applicationA completed college-level course on an official transcript shows admissions and scholarship committees you can do the work.
Study on your own scheduleNo start dates, no deadlines. Watch the lessons, practice, and take the final when you are ready.
130,000+students helped with college creditsACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.

Where the credit goes

Universities that work with NCCRS

System Analysis and Design carries a NCCRS credit recommendation. These universities work with NCCRS; some are UPI Study's signed partners. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.

Thomas Edison State UniversityThomas Edison State UniversitySigned partnerUniversity of the PeopleUniversity of the PeopleSigned partnerCharter Oak State CollegeCharter Oak State CollegeSigned partnerStrayer UniversityStrayer UniversitySigned partnerColumbia Southern UniversityColumbia Southern UniversitySigned partnerIndiana TechIndiana TechSigned partnerPenn StatePenn StateWorks with NCCRSSouthern New Hampshire UniversitySouthern New Hampshire UniversityWorks with NCCRSSUNYSUNYWorks with NCCRS +many more cooperating universitiesFind my college →

Find my college → Not listed? Ask Athena in the corner of this page, or book a 15-minute call.

FAQ

Questions students ask us

About Core Concepts and Structures in Database Design

What is the difference between a file-based system and a database approach?
In a file-based system, each application keeps its own separate data files, while the database approach centralizes data into a shared collection managed by a database management system (DBMS). Separate files store the same data in several places with nothing keeping the copies in agreement, so an address changed in billing may never reach shipping. A DBMS reduces redundancy, maintains consistency centrally, and centralizes access control.
What is the difference between a primary key, a candidate key, and a foreign key?
A candidate key is any column or minimal set of columns that could uniquely identify each record, the primary key is the candidate key actually chosen for that role, and a foreign key is a field that references another table's primary key. For a student, both student ID and email might be candidate keys, with student ID chosen as primary. An enrollment's course ID can be a foreign key referencing the courses table.
What is a composite key in a database?
A composite key is a key made of two or more columns together, used when no single column guarantees uniqueness on its own. In an enrollments table linking students and courses, the primary key can be the combination of student ID and course ID. A student takes many courses and a course has many students, but each pairing appears only once, and each column can also be a foreign key.
What are entity integrity, referential integrity, and domain integrity?
These are integrity constraints a DBMS enforces automatically to keep data accurate and consistent. Entity integrity requires every primary key to be unique and not null. Referential integrity requires a foreign key to either match an existing primary key in the referenced table or be null, which stops an enrollment from referencing a course that does not exist. Domain integrity requires column values to fall within an allowed set, type, or range.

About the System Analysis and Design course

How many college credits is System Analysis and Design?
System Analysis and Design (Computer Science 390) carries a NCCRS credit recommendation for 3 semester credits. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.
How much does System Analysis and Design cost and what is included?
$250, paid once, with lifetime access: all 69 video lessons, a practice quiz after each, the graded work and final exam, and an official transcript when you finish. Students who want several courses choose UPI Study PRO instead.
Which universities accept UPI Study credits?
UPI Study has signed partner agreements with universities such as Thomas Edison State University, University of the People and Charter Oak State College, and works with universities such as Penn State, SNHU and SUNY. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities. Use "Find my college" on this page to check yours.
How long does System Analysis and Design take?
It is self-paced with no deadlines. The 69 lessons total about 9 hours of video; most students add reading and practice time and finish in a few weeks to a few months, on their own schedule.
How are the exams done?
Quizzes and assignments are online in the course. The final exam is taken online with proctoring; a person on the UPI Study team checks assignments, usually within two days.
How do my credits reach my college?
When the course shows 100% complete, request the official transcript from your dashboard. It is sent to the college you name, and you can send it to more than one. Check your college first with "Find my college" on this page, or ask Athena.

Ready for the full course? Earn 3 credits in System Analysis and Design.

69 video lessons, a quiz after each one, and the graded work that earns the credit. Recommended for credit by NCCRS.

Enroll in System Analysis and Design →Download the syllabusEvery course with PRO →
System Analysis and Design
3 credits · NCCRSEnroll →