Approaches to Designing Database Schemas, Explained

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

Approaches to Designing Database Schemas

System Analysis and Design · Chapter 6: Database Design, Normalization, and Business IntelligenceSection 6.3 · lesson 41 of 696 min · free with a free account

← Database Management Systems: Purpose, Functions, and ScopeNext lesson: The Entity-Relationship Model: Diagrams Made Simple →

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

3 schema levels, 3 design stages, 3 approaches, and 2 physical decisions.

  • Explain the three-level schema architecture and data independence
  • Distinguish conceptual, logical, and physical design, and what each produces
  • Compare top-down, bottom-up, and mixed approaches
  • Explain how indexing and partitioning improve performance

Transcript

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

Database Design, Normalization, and Business Intelligence

Designing Database Schemas

With the basics of databases in place, this section is about designing the schema itself. We will look at three levels a database is described at, and three stages of the design process. Then three approaches to reaching a schema. And two physical design decisions that make or break performance, indexing and partitioning.

Why Separate the Work

Change One Thing Without Rebuilding Everything

Designing a database schema is rarely a single step. Analysts separate the work into distinct levels and stages. Why? So that a change in one place does not force a rebuild of the whole database. A new report, a faster disk, or a reorganized table should each be a local change. Not a reason to start over. The rest of this section is about how that separation is achieved.

Figure 1

The Three-Level Schema Architecture

A database is described at three separate levels. At the top is the external level, what each user or application sees. Here, three different views. A sales view, a support view, and a reporting view. All three map to a single conceptual level in the middle. That is the one complete logical structure of the whole database. Its entities, attributes, and relationships. And the conceptual level maps to a single internal level at the bottom. That is how the data is actually stored on disk. Files, indexes, and storage structures. Many views, one logical structure, one physical storage.

Reading the Levels

3 Questions About the Same Data

Each level answers a different question about the same data. The external level describes what one user or application sees, and a database can have many of them. For example, a sales dashboard showing only orders and totals. The conceptual level describes the single, complete logical structure of the whole database, independent of any one user. The internal level describes how the data is physically stored, its file organization, indexes, and storage structures. Separating the levels gives a property called data independence. One level can change without forcing a change to the others, as long as its mapping to the level below still holds. Move the data to a faster disk, and the sales dashboard never notices.

Figure 2

3 Design Stages

The three-level architecture describes a finished database. The design process that produces it moves through three matching stages, each closer to a specific database engine than the last. Conceptual design identifies entities, attributes, and relationships, without regard to any particular technology. It produces an entity-relationship diagram. Logical design translates that conceptual model into a relational schema, of normalized tables, keys, and constraints. Physical design decides how the specific D B M S will store and access that schema. It produces indexes, partitions, and storage settings. Notice the direction. Each stage makes the design more specific, and more tied to real technology.

Figure 3

Top-Down, Bottom-Up, and Mixed

Analysts can reach a conceptual schema from two opposite directions. Top-down design starts from the high-level entities stakeholders already talk about, and then adds attributes and relationships. It works well when the business domain is already well understood. Bottom-up design starts from individual data elements, and then groups and normalizes them upward into entities. It works well when you are starting from existing forms, reports, or spreadsheets. Most real projects blend the two, in a mixed approach. Entities are sketched top-down, then verified and refined bottom-up against real data elements. That combines the speed of top-down with the accuracy of bottom-up.

Figure 4

Physical Design: Indexing and Partitioning

Physical design is where a schema's performance is won or lost. Two of the most common decisions are indexing and partitioning. An index is a separate, sorted structure built on one or more columns. It lets the database jump directly to matching rows, instead of scanning the whole table. It speeds up lookups and searches, but adds overhead to every write, because the index must be updated too. Partitioning splits one large table into smaller, more manageable pieces, often by date range or region. It speeds up queries that target one partition, and simplifies archiving old data. Both have the same aim. Helping the database avoid unnecessary work.

Key Terms

The Vocabulary of This Section

Let's lock in the key terms. The three-level schema architecture separates a database into external, conceptual, and internal levels. Data independence is the ability to change one level without changing the levels above it. Conceptual design identifies entities and relationships. Logical design produces a relational schema. Physical design decides storage and access. Top-down design starts from broad entities. Bottom-up design starts from individual data elements. And indexing builds a sorted lookup structure, while partitioning splits a large table into smaller pieces.

Key Takeaways

What to Carry Forward

To pull this together. The three-level schema architecture separates external views, the conceptual schema, and the internal schema, giving data independence. Conceptual design produces an entity-relationship diagram. Logical design produces a relational schema. Physical design produces indexes, partitions, and storage settings. Top-down design refines broad entities downward. Bottom-up design normalizes individual elements upward. Mixed approaches combine both. And indexing lets a query jump straight to matching rows, while partitioning lets it search only the relevant portion of a table.

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 Approaches to Designing Database Schemas

What is the three-level schema architecture of a database?
The three-level schema architecture describes a database at external, conceptual, and internal levels. The external level is what each user or application sees, and there can be many such views. The conceptual level is the single complete logical structure of the whole database: its entities, attributes, and relationships. The internal level describes how data is physically stored, including files, indexes, and storage structures.
What is data independence in a database?
Data independence is the ability to change one level of a database's schema without forcing changes to the levels above it, as long as the mapping between levels still holds. It comes from separating the external, conceptual, and internal levels. For example, moving data to a faster disk changes the internal level, but a sales dashboard built on an external view does not notice the change.
What is the difference between conceptual, logical, and physical database design?
Conceptual design identifies entities, attributes, and relationships without regard to technology and produces an entity-relationship diagram. Logical design translates that model into a relational schema of normalized tables, keys, and constraints. Physical design decides how a specific DBMS will store and access the schema, producing indexes, partitions, and storage settings. Each stage makes the design more specific and more tied to real technology.
What is the difference between top-down and bottom-up database design?
Top-down design starts from the high-level entities stakeholders already discuss and then adds attributes and relationships, while bottom-up design starts from individual data elements and groups and normalizes them upward into entities. Top-down works well when the business domain is well understood; bottom-up works well when starting from existing forms, reports, or spreadsheets. Many projects use a mixed approach, sketching top-down and verifying bottom-up.

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 →