database design – ERD Help: While Designing a ER Diagram for a basic Car Rental system, Is the reservation entity weak? are all the relationships to this entity weak?

I am trying to design an ER diagram with the following conditions.

Entities and attributes:

CUSTOMER: The CUSTOMER entity holds all information about the customer. It must store customer name, customer ID, phone and physical address.

VEHICLE: The vehicle entity holds all about the car. It must store license plate, vehicle name, model number, vehicle type (SUV, Sedan, Minivan, etc.) and locations at which the vehicle is available.

LOCATION: The LOCATION entity holds all information about the physical address where the cars are available. It must store the location ID and physical address.

RESERVATION: The RESERVATION entity stores all the information about the reservation made by the customer. It must store details like reservation ID, customer ID, license plate, location ID, pickup date and return date.

Relationships:

Each customer might have zero or many reservations, but each reservation must have been made by one and only one customer.
• Each location may have zero or more reservations and each reservation must have one and only one location.
Each reservation must contain one and only one vehicle.
Each customer must have one and only one vehicle and each vehicle should be given to one and only one customer

Notes:
Please identify the primary and foreign keys in the entities.
Show associative entities where applicable.
Differentiate strong and weak relationships in the ER diagram using the appropriate representation.

Can someone please guide me here on creating this ERD

Postgresql on external HD – Database Administrators Stack Exchange

Stack Exchange Network


Stack Exchange network consists of 176 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

How should verification of items in a database be recorded?

I am planning software which includes a relational database of people. For each person, data includes such things as current employer, job title and phone number. Since these are things which may change over time, I want to be able to tell the difference between a fresh record (more likely to be correct) and an old record (less likely to be correct). I could, of course, add a “verified on” data field, but that implies that every item has been verified, and that’s not necessarily the case. For example, if a phone call is made and connects to the right person, we could take that as implicit verification only of the phone number, and not other items such as email or even employer (the company could have been purchased/renamed for instance).

So now I’m thinking that perhaps a better approach would be to have an additional table which has four items: the record ID, a field ID, a date and a boolean result (i.e. TRUE meaning the indicated datum is correct and FALSE indicating it is not, but perhaps the correct value is unknown).

Are there better ways to approach this?

database design – Thoughts on adding a DB collection with a single record for version

I’m building an application with Google Firebase and Firestore that has it’s structure defined in the DB.
This structure is not very big and doesn’t change all that often. This structure is stored in different collections based on hierarchy going something like domain > chapter > unit, where each item has a list of child Ids defined.

To prevent making a ton of DB calls for a structure that doesn’t change all that often I was thinking to keep every entity retrieved from the DB in the browsers localStorage as well as a version for the current application structure. On start it would compare the local version with the DB version and if they don’t match, discard the local version.

I’m thinking to make a collection called ‘system’ or something like that with (for now) a single record called something like ‘composition version’. Each time the structure changes, a cloud function should update this record.

So my question is: does this make sense or are there better ways of doing this in Firebase?

database design – Why if a transaction unlock a data item immediately after its final access of that data item, then serializability may not be ensured

The quote below is from Silberschatz’s Database System Concepts. It says if a transaction unlock a data item immediately after its final access of that data item, serializability may not be
ensured. Can you please explain why serializability may not be ensured?

Transaction Ti may unlock a data item that it had locked at some earlier point.
Note that a transaction must hold a lock on a data item as long as it accesses that
item. Moreover, it is not necessarily desirable for a transaction to unlock a data item
immediately after its final access of that data item, since serializability may not be
ensured.

mvc – Is creating an entity in a database considered to be business logic?

According to my (perhaps incorrect) understanding business logic includes saving/updating entities in the database, as well as connecting with any 3rd party API’s or using services and should therefore reside in the Model in MVC architecture. The controller should call model methods, and is responsible for application-wide logic, like authentication, some global data validation and response to the client.

Because I’m developing in C# I read the official Microsoft article recommendation:

Business logic should reside in services and classes within the Models folder.

However, in another Microsoft sample .NET project creating an entity is done directly in the controller (PostTodoItem). So I’m wondering why isn’t this code inside the model itself? In my opinion the model should contain the method AddTodoItem which inserts a new todo item in the database. In theory adding an entity can be quite complex like validation, updating other models that’s why I thought this code shouldn’t reside directly in the controller.

database – How best to store gigabytes of text for NLP?

I am collecting a very large amount of text and other metadata from fanfiction websites for NLP purposes. Now that I have some, I need an efficient way to store it.

My initial thoughts were that some elements of this collection could fit quite well in a relational database. Every story has an ID, a title, an author, some numerical stats, and a list of tags. But every story also has an estimated average of 50kb of raw text content, with quite a wide variance, and I may be dealing with millions of stories – resulting in hundreds of GBs of raw text data alone. So a relational database alone doesn’t seem like a good solution.

I don’t need or want to be able to search, index, or alter the content of these stories, only the metadata. And this will all be accessed locally. So is there some established process where I could create a database-style directory on one end, for quickly searching the metadata, and some kind of compressed text store on the other end for accessing the story content by ID? Or should I try a different approach entirely?

Ideally, this solution is python compatible.

A graph database suitable for analyzing a heap snapshot?

It looks like recommendation questions aren’t explicitly OT, so here goes:

I’m working on some tooling for analyzing a dump of the heap of a running program. The dump is just a list of nodes with associated metadata and references to other nodes (possibly-cyclical).

I don’t have any experience with graph databases, and I’m wondering if I would save myself a lot of time by building tooling around a graph DB. So I’m looking for recommendations and pointers to resources, and advice.

Some specific questions:

  • are there any graph databases that have functionality built in for computing a dominator tree? (googling this didn’t seem to get any results)
  • are there any DBs that have tooling for visualizing a huge graph?

Query Optimization for Subquery – Database Administrators Stack Exchange

Please help to optimize below MySQL query

SELECT * FROM (
        SELECT u.name,u.avatar,u.occupation, 
        (SELECT GROUP_CONCAT(revenue)FROM LOGS WHERE user_id = u.id AND TYPE ='conversion') AS revenue,
        (SELECT SUM(revenue) FROM LOGS WHERE TYPE ='impression' AND user_id = u.id ) AS totalImpressions,
        (SELECT SUM(revenue) FROM LOGS WHERE user_id = u.id AND TYPE ='conversion') AS totalConversions,
        (SELECT MIN(TIME) FROM LOGS WHERE user_id = u.id ) AS minTime,
        (SELECT MAX(TIME) FROM LOGS WHERE user_id = u.id ) AS maxTime
         FROM users u  
         )q

database – How to effectively sperate CRUD operations from the main SQLiteOpenHelper – android studio

I’m conflicting about how I want my database architecture to be built.
First of all, I use a singleton pattern for the database in order to insure one instance of it (so its thread-safe), and also for me to get a workable database reference wherever I have a context.

All across the application I make many different db operations, for example, some activities need to change only the ‘Meals’ table and some need to change ‘Meals’ & ‘MealFoods’ for example.

For each one of this tables I’ve built a helper class in order to separate the CRUD operations of each table apart from the DatabaseManager class (which extends SQLiteOpenHelper). This, of course is for the sake of simplicity and to get a cleaner code.

First approach:

This approach saves all the helper classes inside the DatabaseManager.

DatabaseManager.java:

public class DatabaseManager extends SQLiteOpenHelper {
    private static String dbName = "logs.db";
    private static final int dbVersion = 1;

    private final Context context;
    public MealsDBHelper mealsDBHelper;
    public MealFoodsDBHelper mealFoodsDBHelper;

    private DatabaseManager(@NonNull Context context) {
        super(context, dbName, null, dbVersio);
        this.context = context.getApplicationContext();//Saving as application context to avoid leaks
        mealsDBHelper = new MealsDBHelper(context);
        mealFoodsDBHelper = new MealFoodsDBHelper(context);
    }

    private static DatabaseManager instance;
    public static synchronized DatabaseManager getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseManager(context);
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //...
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //...
    }
}

Let’s look at the MealsDBHelper class, which pretty much all it does is to help communicate CRUD operations with the database. (For example a user wants to change his meal name)

public class MealsDBHelper {
    public static final String MEALS_TABLE_NAME = "Meals";
    public static final String MEAL_ID_COLUMN = "Meal_ID";
    public static final String MEAL_NAME_COLUMN = "Meal_Name";
    public static final String MEAL_POS_COLUMN = "Meal_Pos";
    public static final String MEAL_DATE_COLUMN = "Date";


    private Context context; //A context object to pass on to the DatabaseManager.getInstance method in all the different methods inside this class
    public MealsDBHelper(Context context){
        this.context = context;
    }

    //For example one of few methods that do operations on the 'Meals' table in the database.
    public void updateMealName(long mealId, String meal_name) {
        UserDataDB.getInstance(context).getWritableDatabase().execSQL("UPDATE " + MEALS_TABLE_NAME + " SET " + MEAL_NAME_COLUMN + " = '" + meal_name + "' WHERE " + MEAL_ID_COLUMN + " = " + mealId);
    }
}

Now, no matter if the activity is modifying 1 or 2 or even 3 tables, I’m able to update the meal’s name like so:

DatabaseManager.getInstance(context).mealsDBHelper.updateMealName(mealId, mealName);

Thats because the DatabaseManager contains a reference to all the other helper classes.

What I like about this approach is that I can simply access every table and do operations on it according to my needs, & what I don’t like is that the DatabaseManager class holds reference for all the helpers, and I’m not sure if its best to do so..

Second approach:

This approach does not saves all the helper classes inside the DatabaseManager.

DatabaseManager.java:

public class DatabaseManager extends SQLiteOpenHelper {
    private static String dbName = "logs.db";
    private static final int dbVersion = 1;

    private final Context context;

    private DatabaseManager(@NonNull Context context) {
        super(context, dbName, null, dbVersio);
        this.context = context.getApplicationContext();//Saving as application context to avoid leaks
    }

    private static DatabaseManager instance;
    public static synchronized DatabaseManager getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseManager(context);
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //...
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //...
    }
}

Now, if my activity needs to modify both ‘Meals’ & ‘MealFoods’ tables I can construct the helpers in onCreate, such as:

public class AddFoodActivity extends AppCompatActivity{

    MealsDBHelper mealsDBHelper;
    MealFoodsDBHelper mealFoodsDBHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(..);

        mealsDBHelper = new MealsDBHelper(this);
        mealFoodsDBHelper = new MealFoodsDBHelper(this);
    }

    //Then whenever I need to modify the table I use:
    mealsDBHelper.updateMealName(mealId, mealName);
}

What I like about this approach is that I can use a simple line to modify a table according to my needs,
& what I don’t like is that I need to define helper references for every activity, and it kind of makes the code inconsistent.

Basically are there any downsides for using one of the methods?

I’ll admit I did leave a big chunk of code out of this post, but its only because I think it won’t add a lot to your understanding of the problem, because its a more general one.

Thank you very much for any kind of help.